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".
lbendlin
2 years agoSuper User
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".
- DavidMDxb2 years agoFrequent Visitor
big thanks, it works