How to get a value from another column in a custom column using power query-CodePudding

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

CodePudding user response：

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 as 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