Forum Discussion

DavidMDxb's avatar
DavidMDxb
Frequent Visitor
2 years ago
Solved

Creating table using DAX and split on a field value

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__G...
  • lbendlin's avatar
    2 years ago

     

    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".