Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
@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.
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
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.
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.
Hi Oscar~
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"
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
This topic has been moved to Power Query forum
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"
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"
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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.
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi, Yes, the number of columns for Attribute Names and Attribute Values is Fixed.
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
@Oscar_Mtz_V , refer if this can help
https://www.thebiccountant.com/2015/08/12/how-to-pivot-multiple-measurescolumns-in-power-query/
@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!
@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
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.