How to extract data in between specifics simbols.

Hi Anonymous ,

Please trake a look at the following. I have explained the steps briefly and added the sample code at the end as well.

Assuming this is your first table named "Waypoints"

and your second table named "Station"

1) On table "Waypoints", Extract text after the first delimiter

This will give you your string after the first "-"

2) Extract text before the last delimiter

This will give you your string before the last"-"

Your data is now in the required format for applying further transformations.

3) Convert the "Text After Delimiter" column into a comma separated list. 

4) Now, on table "Station", perform a group by on column EU and create a list of stations for each value Y or N as shown below

5) Apply filter EU = "Y"

6) On table "Waypoints", perform a fuzzy merge with table "Station" on column "Custom". Set similarity threshold = 0.1 (Very important!)

You will now see that any value in your list of waypoints that matches your list of EU stations will return a match. Everything else will be returned as null

7) Replace null with "N" and rename columns. You will then get your final output

Table : Waypoints

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LYo7DoAgFATvQu229iD4QPlERAMS7n8NMVjsbCaZWlm5LEdxOaafxksNEc4IEpGzNlXmKXDsJseBRVLvLJGGVvZb163cI85m53BmhlzTeE8QzwHyqhftBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Waypoints = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Waypoints", type text}}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Changed Type", "Text After Delimiter", each Text.AfterDelimiter([Waypoints], "-"), type text),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Inserted Text After Delimiter", {{"Text After Delimiter", each Text.BeforeDelimiter(_, "-", {0, RelativePosition.FromEnd}), type text}}),
    #"Added Custom" = Table.AddColumn(#"Extracted Text Before Delimiter", "Custom", each {Text.Replace([Text After Delimiter], "-" , " , ")}),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Merged Queries" = Table.FuzzyNestedJoin(#"Extracted Values", {"Custom"}, Station, {"Custom"}, "Station", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, Threshold=0.1]),
    #"Expanded Station" = Table.ExpandTableColumn(#"Merged Queries", "Station", {"EU"}, {"EU"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Station",null,"N",Replacer.ReplaceValue,{"EU"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Text After Delimiter", "Transit Waypoints"}, {"EU", "EU Transit"}})
in
    #"Renamed Columns"

Table : Station

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WivQNClFQ0lHyU4rViVby9HPxgHOc/IOD4BxvzwgEx9nFPSgEyIsE83zc3RGaPFx9kNlgZRCer6cZnO3i546wJioQwo4FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Station Name" = _t, EU = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Station Name", type text}, {"EU", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"EU"}, {{"Count", each _, type table [Station Name=nullable text, EU=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Station Name]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), " , "), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([EU] = "Y"))
in
    #"Filtered Rows"

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

You have to add as a new query first, and then add {0} to the end of the new query, not the original query named "Station"

This is how your new query should look like.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WivQNClFQ0lHyU4rViVby9HPxgHOc/IOD4BxvzwgEx9nFPSgEyIsE83zc3RGaPFx9kNlgZRCer6cZnO3i546wJioQznb0CXOEmBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Station Name" = _t, EU = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Station Name", type text}, {"EU", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"EU"}, {{"Count", each _, type table [Station Name=nullable text, EU=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Station Name]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), " , "), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([EU] = "Y")),
    Custom1 = #"Filtered Rows"[Custom]{0}
in
    Custom1