Forum Discussion
DavidMDxb
2 years agoFrequent Visitor
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...
- 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".
Ashish_Mathur
2 years agoSuper User
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.