Power查询倾斜数据

Question

我在power查询中遇到了一个问题，我的数据来自一个分成多个页面的报表，其中一些页面将数据倾斜到不同的列。我认为可能有一种基于错误的解决方案，但我希望它更加多余，而不是依赖于文本和数字的纠错。主要是因为有时数据在某些情况下可能是字母，在其他情况下可能是数字。我已经准备了一个数据集，其中随机生成了名称和代码的替代项。我还必须对数据进行一些处理，以给出不同转换的示例，并说明从不同页面拆分的记录。

https://drive.google.com/file/d/0B2qUbAWJXgfyNlByV2RHODJzQjA/view?usp=sharing

数据集中有12条记录，每条记录最终将包含一行。第一页是从源文档中剥离的原始数据。这些是检查历史记录(已屏蔽)，需要移动到每条记录的一行，每个记录有四个特定区域的单独列：

名称、日期、校验数字、etcDeductions

包括名称、日期、记录ID号和金额在内的记录信息是从原始数据中提取和格式化的第一项内容。我在NameData和CheckData中应用的步骤将展示如何提取和格式化这些记录，而且本节中的一些倾斜数据很容易与合并函数和条件列进行协调。

每个单独的付款项目(收入代码、扣减代码或税码)都经过格式化，然后旋转到其自己的列。您可以在收益查询中看到这种策略的示例。PayItemReference查询是我用作付款项目起点的一些基本过滤器。您可以在该查询中看到，代码将从一列转移到另一列，文本和数字混合在一起。代码和它们的值之间可以有空格，也可以没有空格，也可以完全移列。

我正在将代码和它们的值合并到常规列中，然后我可以合并、取消透视、透视等，以获得最终的格式。我尝试过使用条件列和错误，但在原始数据集上，两者都存在小问题。我只需要一些新的视角和数据的新方法。

Answer 1

这是一项具有挑战性的任务。

首先，将表格拆分为多个页面是个好主意，因为每个页面的列结构可能是唯一的。因此，我形成了表格列表，每个表格对应一页。然后我必须处理每一页:提取列名，为每一行添加摘要信息，过滤不需要的行，并设置列名。这是通过使用自定义函数ConvertTable为列表中的每个表完成的。之后，您只需合并结果表即可。

这里：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddRowNum = Table.AddColumn(Table.AddIndexColumn(Source, "Index", 1, 1), "RowNum", each Number.Mod([Index]-1, 52)+1, type number),
    CountTables = {1..(Number.RoundUp(Table.RowCount(AddRowNum)/52, 0))},
    ListTables = List.Transform(CountTables, (ListItem)=>Table.SelectRows(AddRowNum, each [Index] > 52 * (ListItem - 1) and [Index] <= 52 * ListItem)),

    ConvertTable = (tbl as table) as table =>
    let
        hdr1 = Table.Transpose(Table.FillDown(Table.Transpose(Table.FromRecords({tbl{6}})), {"Column1"})),
        hdr2 = Table.FromRecords({tbl{7}}),
        ColNames = Table.Transpose(Table.SelectColumns(Table.FirstN(Table.AddColumn(Table.Transpose(Table.Combine({hdr1, hdr2})), "ColumnName", each [Column1] & ": " & [Column2]), 19), {"ColumnName"})),
        AddPayDate = Table.AddColumn(tbl, "Pay Date", each if [RowNum] > 8 and Text.Trim(tbl{[RowNum]-2}[Column9]) = "Pay Date" then [Column9] else null, type date),
        AddPeriodEndDate = Table.AddColumn(AddPayDate, "Period End Date", each if [RowNum] > 8 and Text.Trim(tbl{[RowNum]-2}[Column12]) = "Period End Date" then [Column12] else null, type date),
        AddJobCode = Table.AddColumn(AddPeriodEndDate, "Job Code", each if [RowNum] > 8 and Text.Trim(tbl{[RowNum]-2}[Column14]) = "Job Code" then [Column14] else null, Int64.Type),
        AddCheckInfo = Table.AddColumn(AddJobCode, "Check Info", each if [RowNum] > 8 and Text.Trim([Column1]) = "Check Printed:" then Table.Transpose(Table.SelectRows(Table.Transpose(Table.FromRecords({_})), each [Column1] <> null)) else null),
        ExpandedCheckInfo = Table.ExpandTableColumn(AddCheckInfo, "Check Info", {"Column4", "Column6", "Column8"}, {"Check Amount", "Direct Deposit", "Net"}),
        FillUp = Table.FillUp(ExpandedCheckInfo, {"Column3", "Check Amount", "Direct Deposit", "Net"})//Table.AddColumn(AddJobCode, "tmp2", each if [RowNum] < 9 then "" else (if Text.Trim([Column1]) = "Check Printed:" then (if [Column3] = null then -1 else [Column3]) else null), type text), {"tmp2"}),
        FillDown = Table.FillDown(FillUp, {"Column1", "Column5", "Pay Date", "Period End Date", "Job Code"}),
        AddCheckEEIDfixed = Table.AddColumn(FillDown, "Check:EEID.fixed", each Text.From([Column5]) & ":" & Text.From([Column3]), type text),
        FilteredExtraRows = Table.SelectRows(AddCheckEEIDfixed, each [RowNum] > 8 and Text.Trim([Column1]) <> "Check Printed:" and Text.Trim([Column7]) <> "PerControl" and Text.Trim(tbl{[RowNum]-2}[Column7]) <> "PerControl" and [#"Check:EEID.fixed"] <> null),
        DemotedHeaders = Table.DemoteHeaders(FilteredExtraRows),
        GetColumnNames1 = Table.Combine({Table.FromRecords({DemotedHeaders{0}}), ColNames}),
        GetColumnNames2 = Table.PromoteHeaders(Table.FillDown(GetColumnNames1, Table.ColumnNames(GetColumnNames1))),
        SetColumnNames = Table.PromoteHeaders(Table.Combine({GetColumnNames2, FilteredExtraRows}))
    in
        SetColumnNames,

    ConvertedList = List.Transform(ListTables, (t) => ConvertTable(t)),
    GetWholeTable = Table.Combine(ConvertedList)
in
    GetWholeTable