Forum Discussion

EricsonCosta93's avatar
EricsonCosta93
Frequent Visitor
7 years ago
Solved

Group collumns from the same table

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!

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    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

     

6 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    7 years ago

    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's avatar
      EricsonCosta93
      Frequent Visitor
      7 years ago

      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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    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.