In a power bi table, I have two columns named: name and value. The name has 3 types: "diameter", "radius", "length". Right now the names are laid out in rows but i would like to have them in separate columns of their own aligned with the item. So I would like to add a column, say diameter, and append the value of the value column to this new column if the value, in the same row, in the name column is "diameter". How would i dothat? Right now I have something like this which is obviously not working:
= Table.AddColumn(#"Filtered Rows5", "Diameter", each if [name] = "Diamter" then [#"[value].Cell.Data.Element: Text"] else "" )
Basically, I would like to transform the table from
to
>Solution :
In powerquery
Add column .. custom column… name diameter
= if [Name] = "diameter" then [Value] else null
Add column .. custom column… name radius
= if [Name] = "radius" then [Value] else null
Add column .. custom column… name length
= if [Name] = "length" then [Value] else null
sample code:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "diameter", each if [Name] = "radius" then [Value] else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "radius", each if [Name]="radius" then [Value] else null),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "length", each if [Name]="length" then [Value] else null)
in #"Added Custom2"
Alternate method
Right click and duplicate the Name Column
Add column index column
Click select the new column
Transform .. pivot column and choose Value and the values column
Resort on the index column and remove it
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Duplicated Column" = Table.DuplicateColumn(#"Added Index", "Name", "Name - Copy"),
#"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[#"Name - Copy"]), "Name - Copy", "Value", List.Sum),
#"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in #"Removed Columns"
benefit: works for any number of unique rows that convert to columns