Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
martysk
Frequent Visitor

Split column into two columns

Hello all,

martysk_1-1693469091719.png

 

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:

martysk_2-1693469298258.png

 

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

 
1 ACCEPTED SOLUTION
mlsx4
Super User
Super User

Hi @martysk 

 

  1. Trim Attribute column (just to avoid extra "space" characters)
  2. Create a conditional column: if attribute = "Excalibur" then return "Solid" and so on...
  3. Remove first 4 rows

View solution in original post

8 REPLIES 8
slorin
Super User
Super User

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

 

 

JoeBarry
Solution Sage
Solution Sage

Hi @martysk 

 

Please provide a screenshot of the table before you unpivoted.

 

thanks

Joe

martysk_0-1693485309950.png

@JoeBarry This is how data looks like before unpivoted (screenshot from excel)

 

mlsx4
Super User
Super User

Hi @martysk 

 

  1. Trim Attribute column (just to avoid extra "space" characters)
  2. Create a conditional column: if attribute = "Excalibur" then return "Solid" and so on...
  3. Remove first 4 rows
martysk
Frequent Visitor

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: 

mlsx4_4-1693485788945.png

As a master table, and then combine values on the original one (having previously filtered out the null values)

martysk
Frequent Visitor

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

 

martysk_0-1693474574165.png

 

wdx223_Daniel
Super User
Super User

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors