Group collumns from the same table

[EricsonCosta93]

Hello guys! 

I'm new on Power BI and DAX, such in this community

Currently I have a dataset with the following structure:

I have to group the ID 2, Project and Value collumns and repeat de ID and Name (first and second collumns) in rows.

The result that I need:

The database I'm working on is much more complex than this example. I have 5 columns of description data and 10 continuations (like ID2, Project and Value) divided into 10 columns each, which I need to do this grouping.

Thanks for the help!

[Ashish_Mathur]

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.

[jdbuchanan71]

Hello @Ashish_Mathur ,

Can you share your sample workbook please?

[Ashish_Mathur]

Hi,

I deleted the sample workbook.  Are you having any trouble with implementing my solution?

[jdbuchanan71]

No, thank you for looking.

[jdbuchanan71 · Accepted solution]

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

[EricsonCosta93]

Thank you!

Worked perfectly

The database had some null or unfilled values. I had to convert numbers to text and replace empty and zero values ​​with "0" to combine columns.

In the end I had problems spliting fields by the delimited, as only the first two columns were appearing. I solved this problem going on advanced options and wiriting the number of collumns.