Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
Need your help again.
I'm trying to create a usable query from a table similar to the one below. Would be great If I could just move the months from row to column then I could just unpivot the OT data.
Cost Center | Geo | Owner | Area | Ave HC | March | March | March | March | March | April | April | April | April | April | |
Regular/Rest Day OT & NP | Holiday | Hol OT XT | Hol RD | Hol RD XT | Regular/Rest Day OT & NP | Holiday | Hol OT XT | Hol RD | Hol RD XT | ||||||
C1 | Geo | Owner1 | Area1 | 144 | 7,978 | 82,297 | 44,498 | 44,396 | 24,727 | 61,430 | 46,659 | 80,813 | 11,750 | 5,218 | |
C2 | Geo | Owner1 | Area1 | 240 | 62,712 | 21,799 | 10,176 | 188 | 8,197 | 23,820 | 45,076 | 89,119 | 90,378 | 81,461 | |
C3 | Geo | Owner2 | Area2 | 103 | 11,654 | 58,234 | 36,018 | 83,229 | 29,414 | 64,546 | 51,583 | 49,051 | 70,932 | 89,198 | |
C1 | Geo | Owner2 | Area2 | 72 | 25,936 | 81,719 | 29,980 | 23,373 | 38,636 | 43,373 | 46,830 | 16,659 | 81,272 | 38,497 | |
C1 | Geo | Owner2 | Area2 | 96 | 66,544 | 65,640 | 89,267 | 111 | 43,360 | 18,892 | 77,386 | 90,483 | 12,679 | 25,721 | |
C2 | Geo | Owner2 | Area2 | 20 | 96,766 | 58,973 | 3,463 | 3,840 | 82,823 | 3,696 | 83,482 | 3,691 | 64,566 | 53,616 | |
C3 | Geo | Owner3 | Area3 | 62 | 9,513 | 49,797 | 56,644 | 54,783 | 36,957 | 11,147 | 64,512 | 64,072 | 87,780 | 53,607 | |
C4 | Geo | Owner2 | Area2 | 74 | 85,864 | 69,627 | 34,539 | 68,356 | 24,908 | 4,119 | 14,908 | 59,342 | 70,100 | 36,873 | |
C2 | Geo | Owner2 | Area2 | 24 | 98,879 | 41,167 | 73,683 | 66,401 | 17,500 | 15,179 | 27,201 | 8,443 | 57,339 | 29,486 |
Proud to be a Super User!
Solved! Go to Solution.
My approach would be to promote row 1 to headers, which will give sequence numbers for duplicate values, then select the other columns (not the months) and unpivot other columns (the months).
Now you're halfway.
From this point: create a replacelist and create the final result.
This video illustrates how it looks ike.
Query Halfway:
let Source = Input, #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Cost Center", "Geo", "Owner", "Area", "Ave HC"}, "Attribute", "Value") in #"Unpivoted Other Columns"
Query ReplaceList:
let Source = Halfway, #"Filtered Rows" = Table.SelectRows(Source, each ([Cost Center] = null)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Cost Center", "Geo", "Owner", "Area", "Ave HC"}), #"Inserted Merged Column" = Table.AddColumn(#"Removed Columns", "NewName", each Text.Combine({Text.Split([Attribute],"_"){0}, [Value]}, "#(tab)"), type text), #"Removed Columns1" = Table.RemoveColumns(#"Inserted Merged Column",{"Value"}), Custom1 = Table.ToRows(#"Removed Columns1") in Custom1
Query Result (which uses List.Accumulate to replace multiple values):
let Source = Halfway, #"Filtered Rows" = Table.SelectRows(Source, each ([Cost Center] <> null)), #"Replaced Values" = List.Accumulate(ReplaceList, #"Filtered Rows", (t, r) => Table.ReplaceValue(t,r{0},r{1},Replacer.ReplaceValue,{"Attribute"})), #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Values", "Attribute", Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv), {"Month", "OT Type"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Month", type text}, {"OT Type", type text}}) in #"Changed Type"
My approach would be to promote row 1 to headers, which will give sequence numbers for duplicate values, then select the other columns (not the months) and unpivot other columns (the months).
Now you're halfway.
From this point: create a replacelist and create the final result.
This video illustrates how it looks ike.
Query Halfway:
let Source = Input, #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Cost Center", "Geo", "Owner", "Area", "Ave HC"}, "Attribute", "Value") in #"Unpivoted Other Columns"
Query ReplaceList:
let Source = Halfway, #"Filtered Rows" = Table.SelectRows(Source, each ([Cost Center] = null)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Cost Center", "Geo", "Owner", "Area", "Ave HC"}), #"Inserted Merged Column" = Table.AddColumn(#"Removed Columns", "NewName", each Text.Combine({Text.Split([Attribute],"_"){0}, [Value]}, "#(tab)"), type text), #"Removed Columns1" = Table.RemoveColumns(#"Inserted Merged Column",{"Value"}), Custom1 = Table.ToRows(#"Removed Columns1") in Custom1
Query Result (which uses List.Accumulate to replace multiple values):
let Source = Halfway, #"Filtered Rows" = Table.SelectRows(Source, each ([Cost Center] <> null)), #"Replaced Values" = List.Accumulate(ReplaceList, #"Filtered Rows", (t, r) => Table.ReplaceValue(t,r{0},r{1},Replacer.ReplaceValue,{"Attribute"})), #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Values", "Attribute", Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv), {"Month", "OT Type"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Month", type text}, {"OT Type", type text}}) in #"Changed Type"
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |