Power Query with multiple header rows

[Moosles]

I have a table of data which is in an awkward format which I'm trying to simplify using Power Query. I'm not very well versed in Power Query so I'm probably missing something obvious here, so I'm hoping you can help! The table of data is in a format like this:

ID Number 1	ID Number 2	Status	Start Date	1 Activity 1	2 Activity 2	3 Activity 3	4 Activity 4	5 Activity 5	6 Activity 6
12345	1	Active	14-Feb-19	300		250
12345	2	Active	11-May-20		160			80	130
12345	3	Completed	3-Jun-20	220			40
67890	1	Active	6-Sep-19	175		350			125
67890	2	Active	11-Nov-19				90
67890	3	Cancelled	6-Apr-20		210
67890	4	Active	9-May-20				110
67890	5	Active	22Feb-21-	200
67890	6	Completed	6-Aug-21		105	265		110

And I want to end up with a simplified table of data like this:

ID Number 1	ID Number 2	Status	Start Date	Activity Number	Activity Name	Cost
12345	1	Active	14-Feb-19	1	Activity Name 1	300
12345	1	Active	14-Feb-19	3	Activity Name 3	250
12345	2	Active	11-May-20	2	Activity Name 2	160
12345	2	Active	11-May-20	5	Activity Name 5	80
12345	2	Active	11-May-20	6	Activity Name 6	130
12345	3	Completed	03-Jun-20	1	Activity Name 1	220
12345	3	Completed	03-Jun-20	4	Activity Name 4	40
67890	1	Active	06-Sep-19	1	Activity Name 1	175
67890	1	Active	06-Sep-19	3	Activity Name 3	350
67890	1	Active	06-Sep-19	6	Activity Name 6	125
67890	2	Active	11-Nov-19	4	Activity Name 4	90
67890	3	Completed	06-Apr-20	2	Activity Name 2	210
67890	4	Active	09-May-20	4	Activity Name 4	110
67890	5	Active	22-Feb-21	1	Activity Name 1	200
67890	6	Completed	06-Aug-21	2	Activity Name 2	105
67890	6	Completed	06-Aug-21	3	Activity Name 3	265
67890	6	Completed	06-Aug-21	5	Activity Name 5	110

The issue is that the Header row in the first table is actually 2 rows, split by Activity Number and Activity Name:

ID Number 1	ID Number 2	Status	Start Date	1	2	3	4	5	6
				Activity Name 1	Activity Name 2	Activity Name 3	Activity Name 4	Activity Name 5	Activity Name 6

So when I end up using the Power Query (and filtering the output) the output is this:

ID Number 1	ID Number 2	Status	Start Date	Activity Number	Cost
12345	1	Active	14-Feb-19	1	300
12345	1	Active	14-Feb-19	3	250
12345	2	Active	11-May-20	2	160
12345	2	Active	11-May-20	5	80
12345	2	Active	11-May-20	6	130
12345	3	Completed	03-Jun-20	1	220
12345	3	Completed	03-Jun-20	4	40
67890	1	Active	06-Sep-19	1	175
67890	1	Active	06-Sep-19	3	350
67890	1	Active	06-Sep-19	6	125
67890	2	Active	11-Nov-19	4	90
67890	3	Completed	06-Apr-20	2	210
67890	4	Active	09-May-20	4	110
67890	5	Active	22-Feb-21	1	200
67890	6	Completed	06-Aug-21	2	105
67890	6	Completed	06-Aug-21	3	265
67980	6	Completed	6-Aug-21	5	110

But I'd like to obtain the Activity Name as well. Once I Close & Load from the Power Query I can apply an HLOOKUP formula in the output to pull the Activity Name, but if I refresh the Power Query then this manual change gets removed. Is there a way that I can pull in the Activity Name directly from the source data table as part of the Power Query as well?

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    ActivityNames = List.Skip(Record.ToList(Source{0}),4),
    OldHeaders = List.LastN(Table.ColumnNames(Source), 6),
    RenameList = List.Generate(()=>0, (x)=> x<List.Count(OldHeaders), (x)=> x+1, (x)=> {OldHeaders{x}, OldHeaders{x} & " - " & ActivityNames{x}}),
    tbl = Table.RenameColumns(Table.Skip(Source,1),RenameList),
    tbl1 = Table.UnpivotOtherColumns(tbl, {"ID Number 1", "ID Number 2", "Status", "Start Date"}, "Activity Name", "Cost"),
    tbl2 = Table.SplitColumn(tbl1, "Activity Name", Splitter.SplitTextByDelimiter(" - ", QuoteStyle.Csv), {"Activity Number", "Activity Name"}),
    Result = Table.TransformColumnTypes(tbl2,{{"Activity Number", Int64.Type}, {"Activity Name", type text}, {"Start Date", type date}})
in
    Result

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2	ID Number 1	ID Number 2	Status	Start Date	1	2	3	4	5	6		ID Number 1	ID Number 2	Status	Start Date	Activity Number	Activity Name	Cost
3					Activity 1	Activity 2	Activity 3	Activity 4	Activity 5	Activity 6		12345	1	Active	2/14/2019	1	Activity 1	300
4	12345	1	Active	2/14/2019	300		250					12345	1	Active	2/14/2019	3	Activity 3	250
5	12345	2	Active	5/11/2020		160			80	130		12345	2	Active	5/11/2020	2	Activity 2	160
6	12345	3	Completed	6/3/2020	220			40				12345	2	Active	5/11/2020	5	Activity 5	80
7	67890	1	Active	9/6/2019	175		350			125		12345	2	Active	5/11/2020	6	Activity 6	130
8	67890	2	Active	11/11/2019				90				12345	3	Completed	6/3/2020	1	Activity 1	220
9	67890	3	Cancelled	4/6/2020		210						12345	3	Completed	6/3/2020	4	Activity 4	40
10	67890	4	Active	5/9/2020				110				67890	1	Active	9/6/2019	1	Activity 1	175
11	67890	5	Active	2/22/2021	200							67890	1	Active	9/6/2019	3	Activity 3	350
12	67890	6	Completed	8/6/2021		105	265		110			67890	1	Active	9/6/2019	6	Activity 6	125
13												67890	2	Active	11/11/2019	4	Activity 4	90
14												67890	3	Cancelled	4/6/2020	2	Activity 2	210
15												67890	4	Active	5/9/2020	4	Activity 4	110
16												67890	5	Active	2/22/2021	1	Activity 1	200
17												67890	6	Completed	8/6/2021	2	Activity 2	105
18												67890	6	Completed	8/6/2021	3	Activity 3	265
19												67890	6	Completed	8/6/2021	5	Activity 5	110
20
Sheet3

 

[VBA learner ITG]

What I did was did exactly as what you did.
I then duplicated the Activity number column and added a prefix to the duplicated column of "Activity Name"

The above is on the assumption that the Activity Number is the same as numerical prefix as the Activity Name.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID Number 1", "ID Number 2", "Status", "Start Date"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Start Date] <> null)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Attribute", "Activity Number"}, {"Value", "Cost"}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Activity Number", "Activity Number - Copy"),
    #"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"ID Number 1", "ID Number 2", "Status", "Start Date", "Activity Number", "Activity Number - Copy", "Cost"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Activity Number - Copy", "Activity Name"}}),
    #"Added Prefix" = Table.TransformColumns(#"Renamed Columns1", {{"Activity Name", each "Activity Name " & _, type text}})
in
    #"Added Prefix"

 

[VBA learner ITG]

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    ActivityNames = List.Skip(Record.ToList(Source{0}),4),
    OldHeaders = List.LastN(Table.ColumnNames(Source), 6),
    RenameList = List.Generate(()=>0, (x)=> x<List.Count(OldHeaders), (x)=> x+1, (x)=> {OldHeaders{x}, OldHeaders{x} & " - " & ActivityNames{x}}),
    tbl = Table.RenameColumns(Table.Skip(Source,1),RenameList),
    tbl1 = Table.UnpivotOtherColumns(tbl, {"ID Number 1", "ID Number 2", "Status", "Start Date"}, "Activity Name", "Cost"),
    tbl2 = Table.SplitColumn(tbl1, "Activity Name", Splitter.SplitTextByDelimiter(" - ", QuoteStyle.Csv), {"Activity Number", "Activity Name"}),
    Result = Table.TransformColumnTypes(tbl2,{{"Activity Number", Int64.Type}, {"Activity Name", type text}, {"Start Date", type date}})
in
    Result

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2	ID Number 1	ID Number 2	Status	Start Date	1	2	3	4	5	6		ID Number 1	ID Number 2	Status	Start Date	Activity Number	Activity Name	Cost
3					Activity 1	Activity 2	Activity 3	Activity 4	Activity 5	Activity 6		12345	1	Active	2/14/2019	1	Activity 1	300
4	12345	1	Active	2/14/2019	300		250					12345	1	Active	2/14/2019	3	Activity 3	250
5	12345	2	Active	5/11/2020		160			80	130		12345	2	Active	5/11/2020	2	Activity 2	160
6	12345	3	Completed	6/3/2020	220			40				12345	2	Active	5/11/2020	5	Activity 5	80
7	67890	1	Active	9/6/2019	175		350			125		12345	2	Active	5/11/2020	6	Activity 6	130
8	67890	2	Active	11/11/2019				90				12345	3	Completed	6/3/2020	1	Activity 1	220
9	67890	3	Cancelled	4/6/2020		210						12345	3	Completed	6/3/2020	4	Activity 4	40
10	67890	4	Active	5/9/2020				110				67890	1	Active	9/6/2019	1	Activity 1	175
11	67890	5	Active	2/22/2021	200							67890	1	Active	9/6/2019	3	Activity 3	350
12	67890	6	Completed	8/6/2021		105	265		110			67890	1	Active	9/6/2019	6	Activity 6	125
13												67890	2	Active	11/11/2019	4	Activity 4	90
14												67890	3	Cancelled	4/6/2020	2	Activity 2	210
15												67890	4	Active	5/9/2020	4	Activity 4	110
16												67890	5	Active	2/22/2021	1	Activity 1	200
17												67890	6	Completed	8/6/2021	2	Activity 2	105
18												67890	6	Completed	8/6/2021	3	Activity 3	265
19												67890	6	Completed	8/6/2021	5	Activity 5	110
20
Sheet3

I would go with Gordan approach.

Hope we have helped.

 

[VBA learner ITG]

If you are using @JGordon11 code and in the future adding more columns to the data set for example "Activity Number: 7" and "Activity Name 7" amend the below line of code:

    OldHeaders = List.LastN(Table.ColumnNames(Source), 6),

to

  OldHeaders = List.LastN(Table.ColumnNames(Source), 7),

 

[JGordon11]

Or if you're always going to have 4 columns before the Activities then the step below will accomodate any number of activities:

OldHeaders = List.Skip(Table.ColumnNames(Source), 4),