PowerQuery - cleansing and transposing multiple headers

[batteredveg]

Hi folks,

I'd be grateful for any help with what should be a really simple set of PowerQuery steps - it's driving me mad as I just cannot get it right.

Rather than (embarrasing myself by) pasting my current set of steps, let me show you what I have to work with (obviously the input document is significantly larger) and what I'm trying to achieve. Ignore the fact the figures are the same for each 'department' - it was just a quick copy/paste job to set the scene.

Essentially I need to cleanse the input, pivot the 'Department' column, and summarise the Figures 1 to 5 for each 'Department':

Power Query Help.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Current CSV import:
2
3	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13	Column14	Column15	Column16	Column17
4		Department	Sales	Sales	Sales	Sales	Sales	IT	IT	IT	IT	IT	Purchasing	Purchasing	Purchasing	Purchasing	Purchasing
5	Company Name	Company Number	Figure 1	Figure 2	Figure 3	Figure 4	Figure 5	Figure 1	Figure 2	Figure 3	Figure 4	Figure 5	Figure 1	Figure 2	Figure 3	Figure 4	Figure 5
6	Quimba	2963520875	$199087.05	$570792.24	$479678.24	$215338.21	$630880.28	$199087.05	$570792.24	$479678.24	$215338.21	$630880.28	$199087.05	$570792.24	$479678.24	$215338.21	$630880.28
7	Centimia	3423440279	$215096.01	$633523.02	$861394.69	$9620.50	$86986.09	$215096.01	$633523.02	$861394.69	$9620.50	$86986.09	$215096.01	$633523.02	$861394.69	$9620.50	$86986.09
8	Tanoodle	8969707689	$828544.93	$225343.97	$47155.50	$955012.74	$822255.72	$828544.93	$225343.97	$47155.50	$955012.74	$822255.72	$828544.93	$225343.97	$47155.50	$955012.74	$822255.72
9	Skippad	8463523196	$947240.25	$475782.54	$231024.55	$923782.09	$638132.10	$947240.25	$475782.54	$231024.55	$923782.09	$638132.10	$947240.25	$475782.54	$231024.55	$923782.09	$638132.10
10	Aivee	4637729386	$921910.87	$813896.35	$262644.73	$271890.69	$258548.49	$921910.87	$813896.35	$262644.73	$271890.69	$258548.49	$921910.87	$813896.35	$262644.73	$271890.69	$258548.49
11
12	What I want to convert this to in PowerQuery:
13
14	Company Name	Company Number	Department	Figure 1	Figure 2	Figure 3	Figure 4	Figure 5
15	Quimba	2963520875	Sales	$199087.05	$570792.24	$479678.24	$215338.21	$630880.28
16	Quimba	2963520875	IT	$199087.05	$570792.24	$479678.24	$215338.21	$630880.28
17	Quimba	2963520875	Purchasing	$199087.05	$570792.24	$479678.24	$215338.21	$630880.28
18	Centimia	3423440279	Sales	$215096.01	$633523.02	$861394.69	$9620.50	$86986.09
19	Centimia	3423440279	IT	$215096.01	$633523.02	$861394.69	$9620.50	$86986.09
20	Centimia	3423440279	Purchasing	$215096.01	$633523.02	$861394.69	$9620.50	$86986.09
21	Tanoodle	8969707689	Sales	$828544.93	$225343.97	$47155.50	$955012.74	$822255.72
22	Tanoodle	8969707689	IT	$828544.93	$225343.97	$47155.50	$955012.74	$822255.72
23	Tanoodle	8969707689	Purchasing	$828544.93	$225343.97	$47155.50	$955012.74	$822255.72
24	Skippad	8463523196	Sales	$947240.25	$475782.54	$231024.55	$923782.09	$638132.10
25	Skippad	8463523196	IT	$947240.25	$475782.54	$231024.55	$923782.09	$638132.10
26	Skippad	8463523196	Purchasing	$947240.25	$475782.54	$231024.55	$923782.09	$638132.10
27	Aivee	4637729386	Sales	$921910.87	$813896.35	$262644.73	$271890.69	$258548.49
28	Aivee	4637729386	IT	$921910.87	$813896.35	$262644.73	$271890.69	$258548.49
29	Aivee	4637729386	Purchasing	$921910.87	$813896.35	$262644.73	$271890.69	$258548.49
Sheet1

Any advice gratefully received!

Best wishes,

P

 

[JGordon11]

maybe

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Depts = List.Distinct(List.RemoveItems(Record.ToList(Source{0}), {"Department", null})),
    cols = List.Buffer(Table.ToColumns(Source)),
    firstcols = List.Buffer(List.FirstN(cols,2)),
    deptcols = List.Buffer(List.Skip(cols,2)),
    lst = List.Buffer(List.Accumulate(Depts, {}, (s,c)=> s & { firstcols & List.Select(deptcols, each _{0} = c) & {{c, "Department", c}}} )),
    lst1 = List.Transform(lst, each Table.FillDown(Table.PromoteHeaders(Table.Skip(Table.FromColumns(_),1)), {"Department"})),
    tbl = Table.FromList(lst1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    tcn = List.Buffer(Table.ColumnNames(tbl[Column1]{0})),
    tbl1 = Table.ExpandTableColumn(tbl, "Column1",tcn),
    reorderlist = List.FirstN(tcn,2) & {"Department"} & List.Range(tcn, 2, List.Count(tcn) -3),
    tbl2 = Table.ReorderColumns(tbl1,reorderlist),
    Result = Table.Sort(tbl2,{{"Company Name", Order.Ascending}, {"Department", Order.Ascending}})
in  
    Result

 

[RoryA]

Or something like:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"Column2", type text}}, "en-GB"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter("~", QuoteStyle.None),"Merged"),
    #"Transposed Table" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"~Department", "Company Name~Company Number"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("~", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[#"Company Name~Company Number"]), "Company Name~Company Number", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute.1", "Company Name"}, {"Attribute.2", "Company number"}, {"~Department", "Department"}})
in
    #"Renamed Columns"

 

[batteredveg]

Thank you folks - much appreciated!