Complex unpivot - column names as rows

[Oscar_Mtz_V]

@AllisonKennedy , thanks for your answer, yes, there are more columns that should not be unpivoted, let's say that there are more attributes of the Programs, but these are shared among programs.

I would go to the DB owners as the last result, rather not.

[Oscar_Mtz_V]

Hi, I need your support in the following.

I have the table on the left and need to transform it into the one on the right.

I am able do to so using the following code and pivoting/unpivoting and filtering, but I am wondering if there is a better, more efficient way?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PROGRAM", type text}, {"Attribute Name 1", type text}, {"Attribute Name 2", type text}, {"Attribute Value 1", Int64.Type}, {"Attribute Value 2", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"PROGRAM", "Attribute Value 1", "Attribute Value 2"}, "Attribute", "Value"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"PROGRAM", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.AfterDelimiter(_, " ", 1), type text}}),
    #"Extracted Text After Delimiter1" = Table.TransformColumns(#"Extracted Text After Delimiter", {{"Attribute.1", each Text.AfterDelimiter(_, " ", 1), type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Extracted Text After Delimiter1", "Custom", each if [Attribute] = [Attribute.1] then true else false),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute", "Attribute.1", "Custom"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Value]), "Value", "Value.1", List.Sum)
in
    #"Pivoted Column"

 One of the challenges is that the attribute names will be changing and new being added.

Additional, would this be easier to do in the source DB? I am connecting to a HIVE DB.

In the link below you will find the excel file of the example (I need to do this in Power BI).

https://1drv.ms/x/s!Aj1t-UWaJ-akgdJBHMs3zhpeYWEGQw?e=efS6jz

In advance thanks.

Regards,

Oscar

[Ashish_Mathur]

Hi,

This M code seems to work

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PROGRAM", type text}, {"Attribute Name 1", type text}, {"Attribute Name 2", type text}, {"Attribute Value 1", Int64.Type}, {"Attribute Value 2", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "PROGRAM", "Attribute Name 1", "Attribute Name 2", "Attribute Value 1", "Attribute Value 2"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Index", "PROGRAM"}, "Attribute", "Value"),
    #"Added Index1" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index.1", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index1", "Custom", each try #"Added Index1" [Value] { [Index.1]  + 2 } otherwise null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Attribute] = "Attribute Name 1" or [Attribute] = "Attribute Name 2")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute", "Index.1", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Value]), "Value", "Custom")
in
    #"Pivoted Column"

Hope this helps.

[littlemojopuppy]

Hi Oscar - here's a link to a solution.  I did something similar last week.

I agree with @AllisonKennedy that because every record has null values for 2/3 of the values something more tabular is probably a better solution.  But if you have to have it as in the picture it should be easy to pivot to get there.

[AllisonKennedy]

Hi Oscar~

@Oscar_Mtz_V 

This looks like two tables placed side by side in one, so you can consider trying to split them into their separate tables and do an append. I'm not convinced you need the PIVOT in the Power Query - could just do this in the visualizations instead, but I have put it in since you asked. 

You'll need three Queries with the following names for this to work, just need to update the Source step with your Excel filepath. 

Table1 Attribute 1

let
Source = Excel.Workbook(File.Contents("C:\\Example Unpivot.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(Table1_Table,{"PROGRAM", "Attribute Name 1", "Attribute Value 1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Attribute Name 1", "Attribute Name"}, {"Attribute Value 1", "Attribute Value"}})
in
#"Renamed Columns"

Table1 Attribute 2

let
Source = Excel.Workbook(File.Contents("C:\Users\Allison.DESKTOP-TFHI78M\Downloads\Example Unpivot.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(Table1_Table,{"PROGRAM", "Attribute Name 2", "Attribute Value 2"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Attribute Name 2", "Attribute Name"}, {"Attribute Value 2", "Attribute Value"}})
in
#"Renamed Columns"

Table1 All

let
Source = Table.Combine({#"Table1 Attribute 1", #"Table1 Attribute 2"}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PROGRAM", type text}, {"Attribute Name", type text}, {"Attribute Value", type number}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"Attribute Name"]), "Attribute Name", "Attribute Value", List.Sum)
in
#"Pivoted Column"

[Oscar_Mtz_V]

@AllisonKennedy , thanks, my biggest issue is that the real case has over 20 columns for Attribute name and the same number for the attribute value.

[AllisonKennedy]

Are the 20 columns named consistently? I think appending the tables is your best and most efficient approach here and could be done using parameters and a custom function if the column names are consistent. As long as you use choose columns, the 20 queries and append should be more efficient than unpivot and pivot you're currently doing.

Other option is to look at the data source, is this an export from somewhere? Can it be exported as 20 different tables? Then you can use built in function with get data from folder in power bi. But this is not needed, custom function can be built just fine.

Let us know naming structure and if there are any other columns in the data.

[Anonymous]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RZA7DoRADMXuMjUFCcyvJDkG4v7X2DhaKcU8WXLhwPu2px3tEWbEADLad/yFMjMGkFniYlYMIKvEzewYQHaJnqEzFtIzjQUaUaPOUylB1KjnBVqCqFHn6VWCqFHn6V2iZ4h6jvZUHujkPT8e0lGGvtMWSGcZDnDiAukqwwVOXSDdZXrG8gTwij/w/QA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PROGRAM = _t, #"Attribute Name 1" = _t, #"Attribute Name 2" = _t, #"Attribute Value 1" = _t, #"Attribute Value 2" = _t]),

    recToTab =(input)=> 
    let
    r=Record.FromList(input[#"Attribute Value 1"]&input[#"Attribute Value 2"],input[#"Attribute Name 1"]&input[#"Attribute Name 2"]),
    tab=Table.FromRecords({r})
    in tab,

    #"Grouped Rows" = Table.Group(Source, {"PROGRAM"}, {{"all", each recToTab(_)}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"A1", "A2", "A3", "A4", "A5", "A6", "A7", "A8", "A9", "A10", "B1", "B2", "B3", "B4", "B5", "B6", "B7", "B8", "B9", "B10", "C1", "C2", "C3", "C4", "C5", "C6", "C7", "C8", "C9", "C10"})
in
    #"Expanded all"

[Anonymous]

code made parametric, respect to the name of new columns:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RZA7DoRADMXuMjUFCcyvJDkG4v7X2DhaKcU8WXLhwPu2px3tEWbEADLad/yFMjMGkFniYlYMIKvEzewYQHaJnqEzFtIzjQUaUaPOUylB1KjnBVqCqFHn6VWCqFHn6V2iZ4h6jvZUHujkPT8e0lGGvtMWSGcZDnDiAukqwwVOXSDdZXrG8gTwij/w/QA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PROGRAM = _t, #"Attribute Name 1" = _t, #"Attribute Name 2" = _t, #"Attribute Value 1" = _t, #"Attribute Value 2" = _t]),

    recToTab =(input)=> 
    let
    r=Record.FromList(input[#"Attribute Value 1"]&input[#"Attribute Value 2"],input[#"Attribute Name 1"]&input[#"Attribute Name 2"]),
    tab=Table.FromRecords({r})
    in tab,

    #"Grouped Rows" = Table.Group(Source, {"PROGRAM"}, {{"all", each recToTab(_)}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", Source[#"Attribute Name 1"]&Source[#"Attribute Name 2"])
in
    #"Expanded all"

[Fowmy]

@Oscar_Mtz_V 


What would differential the columns between Name and Value?
In your example, it's Attribute Name 1... and Attribute Value 1..... One way you can approach this issue is to stack all Attribute Name columns, sort of appending and so the same with values then you will end up with three columns PROGRAM, NAME and VALUE, then the rest is easy.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Oscar_Mtz_V]

@Fowmy thanks for the answer, this is an interesting approach, my concern is that in the real case I have over 20 columns for Attribute name and 20 for the Attribute value.

[Fowmy]

@Oscar_Mtz_V 

If the number of columns is going to be fixed like 20 columns for each attribute and value then I believe it is possible to stack them.
three columns, please confirm if so.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Oscar_Mtz_V]

Hi, Yes, the number of columns for Attribute Names and Attribute Values is Fixed.

[Jimmy801]

Hello @Oscar_Mtz_V 

here an approach that adds a new column with a table that uses the count of attribute-columns as trigger and creates for every pair of attirbutes a new row with name and value. This means that this code is dynamic and you can enter how many pairs of attributes you want, it should always work

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RZA7DoRADMXuMjUFCcyvJDkG4v7X2DhaKcU8WXLhwPu2px3tEWbEADLad/yFMjMGkFniYlYMIKvEzewYQHaJnqEzFtIzjQUaUaPOUylB1KjnBVqCqFHn6VWCqFHn6V2iZ4h6jvZUHujkPT8e0lGGvtMWSGcZDnDiAukqwwVOXSDdZXrG8gTwij/w/QA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PROGRAM = _t, #"Attribute Name 1" = _t, #"Attribute Name 2" = _t, #"Attribute Value 1" = _t, #"Attribute Value 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PROGRAM", type text}, {"Attribute Name 1", type text}, {"Attribute Name 2", type text}, {"Attribute Value 1", Int64.Type}, {"Attribute Value 2", Int64.Type}}),
    HowManyAttributs = List.Count(List.Select(Table.ColumnNames(#"Changed Type"), each Text.Contains(Text.Upper(_), "ATTRIBUTE")))/2,
    CreateNewTableInNewColumn = Table.AddColumn
    (
        #"Changed Type",
        "New Attributes",
        (add)=> Table.FromRecords(List.Range(List.Generate
        (
            ()=> [Counter = 0],
            each [Counter] <= HowManyAttributs,
            (rec)=> 
            [
                Counter = rec[Counter]+1,
                Attribute Name= Record.Field(add, "Attribute Name " & Text.From(rec[Counter]+1)),
                Attribute Value= Record.Field(add, "Attribute Value " & Text.From(rec[Counter]+1))
            ],
            each Record.SelectFields(_,{"Attribute Name", "Attribute Value"})
        ),1))
    ), 
    RemoveAttributes = Table.SelectColumns(CreateNewTableInNewColumn, {"PROGRAM", "New Attributes"}),
    #"Expanded New Attributes" = Table.ExpandTableColumn(RemoveAttributes, "New Attributes", {"Attribute Name", "Attribute Value"}, {"Attribute Name", "Attribute Value"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded New Attributes", List.Distinct(#"Expanded New Attributes"[#"Attribute Name"]), "Attribute Name", "Attribute Value", List.Sum)
in
    #"Pivoted Column"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[amitchandak]

@Oscar_Mtz_V , refer if this can help

https://www.thebiccountant.com/2015/08/12/how-to-pivot-multiple-measurescolumns-in-power-query/

[Oscar_Mtz_V]

@amitchandak thanks, but it doesn't really address the issue of simplifying the current query, where multiple columns hold multiple column names and the same for its values. Cheers!

[amitchandak]

@Oscar_Mtz_V , this seems like unpivot or transpose, but value going to different rows.

Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

See if transpose can help

@ImkeF , can you help on this

[AllisonKennedy]

@Oscar_Mtz_V

Yes, it can be done w/o going to DB owners. You can either continue with your current method of using unpivot, or try the select columns and append approach. I still also think you should save the last 'pivot' step for doing in the report view within the visualizations, unless you need it in Power Query for something else?

Are you able to share a sample table or Excel file with the actual column names (all of them)? If there's patterns in the names, we can create a custom function for this.

PS, I'm also going to move this post to Power Query forum to see if it gets any more visibility from the Power Query specialists. 🙂