Forum Discussion
Split columns by Delimiter and create multiple rows in DAX
Hello,
I have another challange for which I need your assistance.
I have the following table
| Trip Number | Full Itinerary |
| 3564 | (01)GENEVA,GE,SWITZERLAND to SKOPJE,FYRO MACEDONIA > , (02)SKOPJE,FYRO MACEDONIA to GENEVA,GE,SWITZERLAND > |
| 529 | (01)PANAMA CITY,PANAMA to AMSTERDAM,NETHERLANDS > , (02)AMSTERDAM,NETHERLANDS to PARIS,75,FRANCE > , (03)PARIS,75,FRANCE to AMSTERDAM,NETHERLANDS > , (04)AMSTERDAM,NETHERLANDS to PANAMA CITY,PANAMA > |
I want to create a new table in power BI with this result
| Trip number | Origin | Destination | Leg |
| 3564 | GENEVA,GE,SWITZERLAND | SKOPJE,FYRO MACEDONIA | 1 |
| 3564 | SKOPJE,FYRO MACEDONIA | GENEVA,GE,SWITZERLAND | 2 |
| 529 | PANAMA CITY,PANAMA | AMSTERDAM,NETHERLANDS | 1 |
| 529 | AMSTERDAM,NETHERLANDS | PARIS,75,FRANCE | 2 |
| 529 | PARIS,75,FRANCE | AMSTERDAM,NETHERLANDS | 3 |
| 529 | AMSTERDAM,NETHERLANDS | PANAMA CITY,PANAMA | 4 |
I do not know how many items the full itinerary has but the constant delimiter is the ">" and the "to" for origin and destination. If I split this in edit query I get of course many empty columns this is why I want to work by rows.
Does anyone have any ideas?
Thanks,
Mike
HI Mike22,
I don't think they can achieve through dax formula, you can take a look at following query query formula about transform format:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY/LCsIwEEV/ZciqhVnEPhSXQzqtUZOWJFRq7dK1G/1/CxbxUcTdwNxzH30v0nyZCRSRXMQVW24JK0Z/0OHIbk+2gOsF/K5utoxl52owpLiorSY43aRMz4AQySSel4zsvOnEigF7kSfrqUBDlgyB0qHD6R4dyPjAriCDlsPmYeDf0+clI9uQ0x5XOZaOrOJXKo0/n/9kZb+yvto/Zw53", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Trip Number" = _t, #"Full Itinerary" = _t]), #"Added Custom" = Table.AddColumn(Source, "Itinerary", each List.RemoveLastN(List.Transform(Text.Split([Full Itinerary],">"),each Text.Trim(Text.End(_,Text.Length(_)-Text.PositionOf(_,")")-1)," ")),1)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Full Itinerary"}), #"Expanded Itinerary" = Table.ExpandListColumn(#"Removed Columns", "Itinerary"), #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Itinerary", "Itinerary", Splitter.SplitTextByDelimiter("to", QuoteStyle.Csv), {"Origin", "Destination"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Trip Number", Int64.Type}, {"Origin", type text}, {"Destination", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Trip Number"}, {{"Contents", each Table.AddIndexColumn(_, "Index", 1, 1), type table}}), #"Expanded Contents" = Table.ExpandTableColumn(#"Grouped Rows", "Contents", {"Origin", "Destination", "Index"}, {"Origin", "Destination", "Index"}) in #"Expanded Contents"Regards,
Xiaoxin Sheng
6 Replies
HI Mike22,
I don't think they can achieve through dax formula, you can take a look at following query query formula about transform format:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY/LCsIwEEV/ZciqhVnEPhSXQzqtUZOWJFRq7dK1G/1/CxbxUcTdwNxzH30v0nyZCRSRXMQVW24JK0Z/0OHIbk+2gOsF/K5utoxl52owpLiorSY43aRMz4AQySSel4zsvOnEigF7kSfrqUBDlgyB0qHD6R4dyPjAriCDlsPmYeDf0+clI9uQ0x5XOZaOrOJXKo0/n/9kZb+yvto/Zw53", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Trip Number" = _t, #"Full Itinerary" = _t]), #"Added Custom" = Table.AddColumn(Source, "Itinerary", each List.RemoveLastN(List.Transform(Text.Split([Full Itinerary],">"),each Text.Trim(Text.End(_,Text.Length(_)-Text.PositionOf(_,")")-1)," ")),1)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Full Itinerary"}), #"Expanded Itinerary" = Table.ExpandListColumn(#"Removed Columns", "Itinerary"), #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Itinerary", "Itinerary", Splitter.SplitTextByDelimiter("to", QuoteStyle.Csv), {"Origin", "Destination"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Trip Number", Int64.Type}, {"Origin", type text}, {"Destination", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Trip Number"}, {{"Contents", each Table.AddIndexColumn(_, "Index", 1, 1), type table}}), #"Expanded Contents" = Table.ExpandTableColumn(#"Grouped Rows", "Contents", {"Origin", "Destination", "Index"}, {"Origin", "Destination", "Index"}) in #"Expanded Contents"Regards,
Xiaoxin Sheng
Thanks Anonymous
for your usual very detailed reply.
Unfortunately I am getting the error below
Expression.Error: We cannot convert the value null to type Text. Details: Value= Type=TypeWhen on the step
= Table.Group(#"Changed Type", {"Trip Number"}, {{"Contents", each Table.AddIndexColumn(_, "Index", 1, 1), type table}}),I checked and there are no null values in the trip number do you have any idea of what it could be?
Thanks a lot.Mike
HI Mike22,
Can you please share your pbix file with sample source data to test?
Regards,
Xiaoxin Sheng
