Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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)))