Creating table using DAX and split on a field value

[DavidMDxb]

hi Everyone

i ve been searching without find the answer, if can get help please

I have a tab le with the below (example)

RouteID	A Point	B Point	SegmentHeader
1	GPSA	GPSB	__GPS3__GPS4__GPS5__ ( can be many GPS points here)

i need to create a table with the below results (rows results)

RouteID = 1 

RouteID	A Point	B Point
1	GPSA	GPS3
1	GPS4	GPS5
1	GPS5	GPSB

I guess there is a while to go thru the first table, then a split on the ___ separator, but honestly, i m a bit lost

someone to help ?

thanks!!!

david

[Ashish_Mathur]

Hi,

Your data does not paste properly in an MS Excel file.  Put both those tables in an MS Excel file and share the download link of that file.

[lbendlin · Accepted solution]

I don't think you will want to do that in DAX.  Here is a Power Query version.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WMlTSUXIPCHaEUE5AKj4eyDAGkyZg0jQ+Xik2FgA=", BinaryEncoding.Base64),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [RouteID = _t, #"A Point" = _t, #"B Point" = _t, SegmentHeader = _t]
  ),
  #"Added Custom" = Table.AddColumn(
    Source,
    "Custom",
    each List.Zip(
      {
        {[A Point]} & List.RemoveItems(Text.Split([SegmentHeader], "__"), {""}),
        List.RemoveItems(Text.Split([SegmentHeader], "__"), {""}) & {[B Point]}
      }
    )
  ),
  #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
  #"Extracted Values" = Table.TransformColumns(
    #"Expanded Custom",
    {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}
  ),
  #"Removed Other Columns" = Table.SelectColumns(#"Extracted Values", {"RouteID", "Custom"}),
  #"Split Column by Delimiter" = Table.SplitColumn(
    #"Removed Other Columns",
    "Custom",
    Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false),
    {"A Point", "B Point"}
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Split Column by Delimiter",
    {{"RouteID", Int64.Type}, {"A Point", type text}, {"B Point", type text}}
  )
in
  #"Changed Type"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[DavidMDxb]

big thanks, it works