Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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"
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 ( ) 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
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
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?
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 ( ) 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
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"
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |