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.
Hello all,
Im trying to split the data in a column "Value" into two collumns:
1. Value - will contains numbers only
2. Type - will contains all the text (Solid, Liquid, etc) in line with the corresponding Attribute
Table which I want to achieve should look like this:
Ive tried Split column option, but cant make it work.
It works when using Column from example, but when I add new items (attribute and value), it wont recognize and assign wrong value to Attribute - so no GO option for me.
Any idea is appreciated.
Thanks
Solved! Go to Solution.
Hi @martysk
Hi,
let
Source = Your_Source,
Custom1 = List.RemoveFirstN(Table.ColumnNames(Source),5),
Custom2 = List.RemoveFirstN(Record.ToList(Source{0}),5),
Data = Table.AddColumn(Source, "Data", each Table.FromColumns({ Custom1 , Custom2, List.RemoveFirstN(Record.ToList(_),5)})),
Custom3 = Table.RemoveColumns(Data,Custom1)
in
Custom3
then Expand
Stéphane
Hi @martysk
Thanks @mlsx4,
I understand the point of making the conditional columns, but in the future I will have more new attributes + values added and I want system to automatically recognize and link it with new value
And the point of create a duplication of the table after unpivot, filter (null values) for just keeping this:
As a master table, and then combine values on the original one (having previously filtered out the null values)
Thanks @wdx223_Daniel
Ive tried to add custom column with the command u have provided, but it doesnt aligned right attributes with right values (ie: Excalibur is linked with Liquid, but should be Solid)
see picture below
NewStep=Table.AddColumn(Table.Skip(#"Unpiovted Other Columns",4),"Type",Function.ScalarVector(Value.Type(each _) as any,(t)=>let a=#"Unpiovted Other Columns"[Value] in List.Repeat(List.FirstN(a,4),List.Count(a)/4-1)))
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.