Forum Discussion

Mike22's avatar
Mike22
Helper III
8 years ago
Solved

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)S...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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's avatar
Mike22
Helper III
8 years ago

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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

HI Mike22,

 

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

 

Regards,

Xiaoxin Sheng

  • Mike22's avatar
    Mike22
    Helper III
    8 years ago

    Anonymous

     

    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's avatar
      Mike22
      Helper III
      7 years ago

      Hello Anonymous

       

      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