Forum Discussion
Unpivotting multiple columns - Power query
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
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
PivotStéphane
3 Replies
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
PivotStéphane
Thank you so much for this, has worked like a charm for the multiple columns.
Hello ,
I have a scenario maybe it can help you :
- Duplicate your table.
- 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.
- 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.
- Append both tables together and rename the headers as desired.
