Split columns by Delimiter and create multiple rows in DAX

[Mike22]

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

[Anonymous]

Hey,

I am trying to split 3 columns by delimiter.  How can I create 2 rows with the new info after the & delimiter without duplicating everything 6 times?

My data looks like this

Installed model                         Capacity                    Category

Unit A & Unit B                       5.00 & 2.50                      2 & 1

Should look like

Unit A                                    5.00                                    2

Unit B                                      2.5                                    1

[v-shex-msft · Accepted solution]

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

[Mike22]

Thanks @v-shex-msft

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=Type

When 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

[v-shex-msft]

HI @Mike22,

Can you please share your pbix file with sample source data to test? 

Regards,

Xiaoxin Sheng

[Mike22]

@v-shex-msft

I was about to write to you, I just found the issue. The excel source file had 2 completely blank rows that could not be seen as null when filtering on the row but were impacting the calculation.

Thanks a lot for another great solution.

Mike

[Mike22]

Hello @v-shex-msft

I am sorry to go back to this topic which was resolved. I have a quick question as I continue my work on this dataset. I would like to now represent this data on a map showing the various flight paths. Ideally, I want also to show the bubble on each city to count the number of flights to the specific city. I do not want to count the last leg back to the origin, but I want to still display it on the map.

I have seen some custom maps that show flight paths but I am not sure how to adapt my data and also which one is the best to show what I am aiming for.

Thanks a lot in advance for any help,

Mike