Usable Query from a file with nested headers

[danextian]

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

[MarcelBeug · Accepted 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"