cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Piotr Frequent Visitor
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

Accepted Solutions
Highlighted
Super User
Super User

Re: Table transform

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"

 

rohitMe Regular Visitor
Regular Visitor

Re: Table transform

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

4 REPLIES 4
rohitMe Regular Visitor
Regular Visitor

Re: Table transform

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
Frequent Visitor

Re: Table transform

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

 

 

 

Highlighted
Super User
Super User

Re: Table transform

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"

 

rohitMe Regular Visitor
Regular Visitor

Re: Table transform

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