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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Piotr
Frequent Visitor

Table transform

Hi,

 

I have table like this:

 

Name

Ids

Type

John

123, 789,777

passport,id,car_license

Wiki

434, 676

tank id, job id

 

*values divided by comma

 

 

and want to convert to table like this:

 

Name

Ids

Type

John

123

passport

John

789

id

John

777

car_license

Wiki

434

tank id

Wiki

676

job id

 

How to manage this in Power query?

 

It's possible to do that in DAX?

 

Link to real data: Real table

2 ACCEPTED SOLUTIONS
Stachu
Community Champion
Community Champion

This may be more efficient, you need to replace the PreviousStepName with the last step in your Power Query flow

    #"Added Custom" = Table.AddColumn(PreviousStepName, "Custom", each List.Zip({Text.Split([Ids],","),Text.Split([Type],",")})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Ids", "Type"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Ids", "Type"}),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Ids", Text.Trim, type text}, {"Type", Text.Trim, type text}})
in
    #"Trimmed Text"

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

let   
Source = Excel.Workbook(File.Contents("C:\Users\Rohit Mittel\Desktop\try.xlsx"), null, true),
Table2_Table = Source{[Item="Table2",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table2_Table,{{"Name", type text}, {"Ids", type text}, {"Type", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "IdsSplit", each Text.Split([Ids],",")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "TypeSplit", each Text.Split([Type],",")),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "AllSplit", each Table.FromColumns({[IdsSplit],[TypeSplit]})),
#"Expanded Custom.2" = Table.ExpandTableColumn(#"Added Custom2", "AllSplit", {"Column1", "Column2"}, {"Column1", "Column2"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom.2",{"Name", "Column1", "Column2"})
in
#"Removed Other Columns"

Try the above code(Paste it in your advance editor). "Text.Split() function is used to split columns to rows.

 

The steps to achieve the above code are:


 1. Add custom column to split Ids (Using Text.Split()) 
 2. Add custom column to split Type(Using Text.Split())
 3. Combine the above two list to form a table (Using Table.FromColumns() )
 4. Click the expand icon ( e46f02a2-febb-424c-8b23-64991ef9cba1.png ) in the column header
 5. Select the columns you want to keep and remove others

 

Syntax for Text.Split() Function:   Text.Split([Column Name],"delimiter")
Syntax for Table.FromColumns() Function:    Table.FromColumns({[Column Name1],[Column Name2],....})

 

Hope this solution is useful and thanks, your question also helped me learn something new. There may be an even more efficient way to do it if you know the coding language used in power query.

 

Regards

Rohit

View solution in original post

4 REPLIES 4
rohitMe
Advocate I
Advocate I

Hi @Piotr

 

Try applying the following steps in the Power Query:

 

1. Transpose the table 

2. Split the columns by delimiter

3. Transpose the table again

4. Promote first row to headers

5. Fill down in the first column

 

The solution will give you the result you need for the given example. Also I do not think there is anyhting in DAX which you can use in order to acheive the result that you want.

 

Hope this solution was useful.

 

Regards

Rohit

Piotr
Frequent Visitor

Hi, thanks, this works for small table.

 

But really I need solution for table with thousands of rows like that in my first message.

 

Any idea?

 

Real Table

 

 

 

let   
Source = Excel.Workbook(File.Contents("C:\Users\Rohit Mittel\Desktop\try.xlsx"), null, true),
Table2_Table = Source{[Item="Table2",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table2_Table,{{"Name", type text}, {"Ids", type text}, {"Type", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "IdsSplit", each Text.Split([Ids],",")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "TypeSplit", each Text.Split([Type],",")),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "AllSplit", each Table.FromColumns({[IdsSplit],[TypeSplit]})),
#"Expanded Custom.2" = Table.ExpandTableColumn(#"Added Custom2", "AllSplit", {"Column1", "Column2"}, {"Column1", "Column2"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom.2",{"Name", "Column1", "Column2"})
in
#"Removed Other Columns"

Try the above code(Paste it in your advance editor). "Text.Split() function is used to split columns to rows.

 

The steps to achieve the above code are:


 1. Add custom column to split Ids (Using Text.Split()) 
 2. Add custom column to split Type(Using Text.Split())
 3. Combine the above two list to form a table (Using Table.FromColumns() )
 4. Click the expand icon ( e46f02a2-febb-424c-8b23-64991ef9cba1.png ) in the column header
 5. Select the columns you want to keep and remove others

 

Syntax for Text.Split() Function:   Text.Split([Column Name],"delimiter")
Syntax for Table.FromColumns() Function:    Table.FromColumns({[Column Name1],[Column Name2],....})

 

Hope this solution is useful and thanks, your question also helped me learn something new. There may be an even more efficient way to do it if you know the coding language used in power query.

 

Regards

Rohit

Stachu
Community Champion
Community Champion

This may be more efficient, you need to replace the PreviousStepName with the last step in your Power Query flow

    #"Added Custom" = Table.AddColumn(PreviousStepName, "Custom", each List.Zip({Text.Split([Ids],","),Text.Split([Type],",")})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Ids", "Type"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Ids", "Type"}),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Ids", Text.Trim, type text}, {"Type", Text.Trim, type text}})
in
    #"Trimmed Text"

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.