Excel Power Query：如何将巨大的表格逆透视并转置为可读格式以进行分析

I have this table that looks similar to this:

我想将其改造为如下所示：

这个想法是对表进行逆透视（或转置），以便可以将其输入到其他 BI 工具中，并且可以读取以进行分析。

我有大约 20 个这样的表，有 100 多个列，所以当然手动完成几乎是不可能的。

如何使用 PowerQuery 完成此操作？我尝试过使用 unpivot 功能，但我被卡住了，因为它显示 NYC1、NYC2 等。VBA、宏也不起作用。任何其他建议都表示赞赏，但我现在束手无策。帮助！

这是一种非常通用的 depivot 方法，可以处理多个行/列标题。

在运行之前选择源表中的一个单元格（注意 - 这使用CurrentRegion如果您的表有完全空白的行或列，那么将会失败）。

Sub UnpivotIt()

    Dim numRowHeaders As Long, numColHeaders As Long
    Dim numRows As Long, numCols As Long, rng As Range
    Dim rngOut As Range, r As Long, c As Long, i As Long, n As Long
    Dim arrIn, arrOut, outRow As Long

    arrIn = Selection.CurrentRegion.Value

    numRowHeaders = Application.InputBox("How many header rows?", Type:=1)
    numColHeaders = Application.InputBox("How many header columns?", Type:=1)
    Set rngOut = Application.InputBox("Select output (top-left cell)", Type:=8)

    Set rngOut = rngOut.Cells(1) 'in case >1 cells selected

    numRows = UBound(arrIn, 1)
    numCols = UBound(arrIn, 2)

    ReDim arrOut(1 To ((numRows - numRowHeaders) * (numCols - numColHeaders)), _
                 1 To (numRowHeaders + numColHeaders + 1))

    outRow = 0
    For r = (numRowHeaders + 1) To numRows
    For c = (numColHeaders + 1) To numCols
        'only copy if there's a value
        If Len(arrIn(r, c)) > 0 Then
            outRow = outRow + 1
            i = 1
            For n = 1 To numColHeaders 'copy column headers
                arrOut(outRow, i) = arrIn(r, n)
                i = i + 1
            Next n
            For n = 1 To numRowHeaders '...row headers
                arrOut(outRow, i) = arrIn(n, c)
                i = i + 1
            Next n
            arrOut(outRow, i) = arrIn(r, c) '...and the value
        End If
    Next c
    Next r

    rngOut.Resize(outRow, UBound(arrOut, 2)).Value = arrOut

End Sub