Unpivotting multiple columns - Power query

[SloeMo]

Hi all, I have a sharepoint list with multiple columns in that I am tryting to pivot into a usable format. So far I have tried lots of steps which have all failed with the result of me creating duplcate queries and then appending to 1 table which is inefficient. I have tried following steps from other suggestions but cant seem to get it right

The data looks like this although there would be 22 Areas not just the 2 in my example below, each area has 4 columns attributed to them - Owner, Agreed, Date Agreed and commentary.

Programme	Business Case Name	Business Case Owner	Area 1 Owner	Area 1 Agreed	Area 1 Date Agreed	Area 1 Commentary	Area 2 Owner	Area 2 Agreed	Area 2 Date	Area 2 Commentary
Test Programme	Lee Test	Lee Morgan	Lee Morgan	Yes	30/06/2023	text in here	Lee Morgan	null	null	Random text
Test Programme 2	Lee Test 2	Lee Morgan	Lee Morgan	null	null	Different text	Lee Morgan	null	null	null

What I am trying to achieve is something like the following format.

Programme	Business Case Name	Business Case Owner	Area	Area Owner	Agreed	Date Agreed	Commentary
Test Programme	Lee Test	Lee Morgan	Area 1	Lee Morgan	Yes	30/06/2023	text in here
Test Programme	Lee Test	Lee Morgan	Area 2	Lee Morgan	null	null	Random text
Test Programme 2	Lee Test 2	Lee Morgan	Area 1	Lee Morgan	null	null	Different text
Test Programme 2	Lee Test 2	Lee Morgan	Area 2	Lee Morgan	null	null	null

Any help guidance is greatly appreciated.

Many thanks

Lee

[SloeMo]

Thank you so much for this, has worked like a charm for the multiple columns.

[slorin · Accepted solution]

Hi

Unpivot columns, Split Attribute, Pivot

let
Source = YourSource,
Unpivot = Table.UnpivotOtherColumns(Source, {"Programme ", "Business Case Name", "Business Case Owner"}, "Attribute", "Value"),
#"Split Column" = Table.SplitColumn(Unpivot, "Attribute", Splitter.SplitTextByCharacterTransition({"0".."9"}, {" "}), {"Attribute.1", "Attribute.2"}),
Trim = Table.TransformColumns(#"Split Column",{{"Attribute.2", Text.Trim, type text}}),
Pivot = Table.Pivot(Trim, List.Distinct(Trim[Attribute.2]), "Attribute.2", "Value")
in
Pivot

Stéphane 

[HoussemDhahri]

Hello , 

I have a scenario maybe it can help you :

1. Duplicate your table.
2. Remove all features related to Area 2 from the first table. Instead, add a new column called 'Area' and fill it with the value 'Area1' for all rows.
3. Remove all features related to Area 1 from the second table. Instead, add a new column called 'Area' and fill it with the value 'Area2' for all rows.
4. Append both tables together and rename the headers as desired.