Forum Discussion
Group collumns from the same table
Hello EricsonCosta93
We can get to what you are looking for by doing some work in the PowerQuery editor.
- Add a custom column for each of the 10 blocks of 5 fields that combines the 5 fields together with a delimeter.
Once we have all 10 blocks of data combined into 10 fields we can
- Remove the original data fields
- Unpivot the new fields
- Split the field on the delimiter
- Rename the split column
Just our 2 id columns and 10 columns of combined data
10 columns of combined data unpivoted
Split on "|" then rename
I created an example PBIX file with the 52 columns of data and the transformation steps for you to look at.
https://www.dropbox.com/s/04g5s5grs88tain/unpivot%20example.pbix?dl=0
If you go into Home > Edit Queries you will be able to see the transformation steps I appiled and if you click on each step it will show you what I did on that step.
The main work is done in the "Add Custom" steps where I make the combining fields. You can click on the gear next to the step to bring up the editor for that step.
And here is an example of the final result
Result used in a table in PowerBI
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"ID 2", Int64.Type}, {"Project", type text}, {"Value", Int64.Type}, {"ID 2.1", Int64.Type}, {"Project 2.1", type text}, {"Value 2.1", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "Name"}, "Attribute", "Value.1"),
#"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Other Columns", "Attribute", "Attribute - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Attribute - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute - Copy.1", "Attribute - Copy.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute - Copy.1", type text}, {"Attribute - Copy.2", type number}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute - Copy.2"}),
Partition = Table.Group(#"Removed Columns", {"Attribute - Copy.1"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"ID", "Name", "Attribute", "Value.1", "Index"}, {"ID", "Name", "Attribute", "Value.1", "Index"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Partition",{"Attribute"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns1","ID","ID2",Replacer.ReplaceText,{"Attribute - Copy.1"}),
#"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[#"Attribute - Copy.1"]), "Attribute - Copy.1", "Value.1"),
#"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
#"Removed Columns2"
Hope this helps.
Hello Ashish_Mathur ,
Can you share your sample workbook please?
Hi,
I deleted the sample workbook. Are you having any trouble with implementing my solution?
No, thank you for looking.