使用 PowerQuery，我從 CSV 匯入資料，如下所示：

Report Title,,,,,
,Date,Type,USER_ID,PICKED_QTY,No of Hours
,31/10/2021,Type A,User_1,300,3
,31/10/2021,Type A,User_3,250,8
,01/11/2021,Type B,User_1,167,5
,01/11/2021,Type C,User_2,988,2
,02/11/2021,Type A,User_1,1113,4
Date,Type,USER_ID,PICKED_QTY,No of Hours,
03/11/2021,Type C,User_1,1500,5,
04/11/2021,Type A,User_1,200,8,    

有時它看起來像這樣（沒有第二頁） - 這就是問題所在：

Report Title,,,,,
,Date,Type,USER_ID,PICKED_QTY,No of Hours
,31/10/2021,Type A,User_1,300,3
,31/10/2021,Type A,User_3,250,8
,01/11/2021,Type B,User_1,167,5
,01/11/2021,Type C,User_2,988,2
,02/11/2021,Type A,User_1,1113,4

我使用這個 PQ 將資料轉換為可讀格式（這個來源會有所不同，但為了簡單起見，這里參考了一個表格）：

資料源：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitData = Table.SplitColumn(Source,"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv))
in
    SplitData 

然后我使用兩個查詢來排列資料，所以日期列都在同一列中，等等。

查詢 1：

let
    Source = DataSource,
    RemoveTopRows = Table.Skip(Source,1),
    PromoteHeaders = Table.PromoteHeaders(RemoveTopRows, [PromoteAllScalars=true]),
    FilterRows = Table.SelectRows(PromoteHeaders, each ([#""] = "")),
    RemoveOtherColumns = Table.SelectColumns(FilterRows,{"Date", "Type", "USER_ID", "PICKED_QTY", "No of Hours"}),
    ChangeType = Table.TransformColumnTypes(RemoveOtherColumns,{{"Date", type date}, {"Type", type text}, 
                                                                {"USER_ID", type text}, {"PICKED_QTY", Int64.Type}, 
                                                                {"No of Hours", Int64.Type}})
in
    ChangeType  

查詢 2：

let
    Source = DataSource,
    RemoveTopRows = Table.Skip(Source,1),
    FilterRows = Table.SelectRows(RemoveTopRows, each ([Column1.1] <> "")),
    PromoteHeaders = Table.PromoteHeaders(FilterRows, [PromoteAllScalars=true]),
    RemoveOtherColumns = Table.SelectColumns(PromoteHeaders,{"Date", "Type", "USER_ID", "PICKED_QTY", "No of Hours"}),
    FilterRows2 = Table.SelectRows(RemoveOtherColumns, each ([Date] <> "Date")),
    ChangeType = Table.TransformColumnTypes(FilterRows2,{{"Date", type date}, {"Type", type text}, {"USER_ID", type text}, 
                                                         {"PICKED_QTY", Int64.Type}, {"No of Hours", Int64.Type}})
in
    ChangeType  

最后，我將前兩個查詢連接在一起并分組以獲得最終表格。

查詢 3：

let
    Source = Query1,
    AppendQueries = Table.Combine({Source, Query2}),
    SortRows = Table.Sort(AppendQueries,{{"Date", Order.Ascending}}),
    GroupRows = Table.Group(SortRows, {"Date", "Type"}, {{"Picked Qty", each List.Sum([PICKED_QTY]), type nullable number}, 
                                                         {"Total Hours", each List.Sum([No of Hours]), type nullable number}}),
    AddDivision = Table.AddColumn(GroupRows, "Rate", each [Picked Qty] / [Total Hours], type number)
in
    AddDivision

題

有時我的原始資料不包括第二頁資料，因此不需要 Query2。
發生這種情況時，如果我不手動添加第二頁的標題，則會出現錯誤： [Expression Error] The column 'Date' of the table wasn't found.

我如何避免這種情況？錯誤出現在Query2with RemoveOtherColumns- 沒有列標題時，它找不到正確的列，并且Query3因為它無法附加回傳錯誤的查詢。

uj5u.com熱心網友回復：

無需重寫所有內容，您只需將 Query2 的最后一行更改為

in try ChangeType otherwise Table.FromRecords({[Date = null, Type = null, USER_ID=null, PICKED_QTY=null, No of Hours = null]})

或者

in try ChangeType otherwise Table.Skip(Table.FromRecords({[Date = null, Type = null, USER_ID=null, PICKED_QTY=null, No of Hours = null]}),1)

創造：

或者只在一個查詢中完成整個事情

let Source = Csv.Document(File.Contents("C:\temp2\data.csv"),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Removed Top Rows" = Table.Skip(Source,1),
#"Filtered Rows" = Table.PromoteHeaders(Table.SelectRows(#"Removed Top Rows", each [Column1] = ""), [PromoteAllScalars=true]),
#"Filtered Rows2" = Table.PromoteHeaders(Table.SelectRows(#"Removed Top Rows", each [Column1] <> ""), [PromoteAllScalars=true]),
AppendQueries = Table.Combine({#"Filtered Rows",#"Filtered Rows2"}),
SortRows = Table.Sort(AppendQueries,{{"Date", Order.Ascending}}),
#"Changed Type1" = Table.TransformColumnTypes(SortRows,{{"PICKED_QTY", type number}, {"No of Hours", type number}}),
GroupRows = Table.Group(#"Changed Type1", {"Date", "Type"}, {{"Picked Qty", each List.Sum([PICKED_QTY]), type number}, {"Total Hours", each List.Sum([No of Hours]), type number}}),
AddDivision = Table.AddColumn(GroupRows, "Rate", each [Picked Qty] / [Total Hours], type number)
in AddDivision

標籤：擅长 电源查询

上一篇：單擊按鈕插入行

下一篇：從選定單元格的行中獲取值