How to extract data in between specifics simbols.

[Anonymous]

Hello! 

I am new to Power BI and I am needing to be able to extract the data after the first "-" and the last "-" . Then I need to VLOOKUP of what is left to  a table to be able to identify each one of them with a label that says " TRANSIT EU" "NO TRANSIT EU"
How can i do this? I the VLOOKUP going to work with the remaining "-'"?  

Desire table: 

Thanks!

[rohit_singh · Accepted solution]

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! 🙂

[Anonymous]

Hi, me again.

I see an error here.
These is my "station list" already filtered and everything

This is my merge set up ( exactly like you showed me) 

And these is what i received. I see "Y" where it shouldt be, basically everything has an "Y"

What could be the error here?

[rohit_singh]

Hi @Anonymous,

I'm not sure what your exact requirement is. The code I sent you performs a match between transit waypoints and the station list. If any of the waypoints exist on the station list, it will return 'Y' else null.

For the case highighted, TPERT, ANCA, MEMH, AUSR  the merge will return a 'Y' if any of these waypoints are in your station list. Can you confirm that this is your equirement? If not, what is your expected output?

What items are in your station list?

Kind regards,

Rohit

[Anonymous]

Exaclty , non of those statios are "Y" . all of them are N. 
This is my experctation IF any of this stations is on my waypoint column ( in this case "COSTUM" after delimiter etc. etc.) 

Its like is posting everytime "Y"for every line. 

[rohit_singh]

Hi @Anonymous ,

Thanks for your reply. In that case I will show you a better approach. Apologies we should have gone with this one earlier : 

1) Open your Station query, right click on the Custom column and click on add as new query

2) You will notice that a new query has now been added. In the formula bar, add {0} at the end of the step as shown below. I rename this query as "StationList". 

What this step does is that it takes all stations that have EU value as "Y" and appends them into a list. This will now be your lookup list.

3) Modify your Waypoints query as shown 

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.Split(Text.Replace([Text After Delimiter], "-" , " , "), " , ")),
    #"Added Custom.1" = Table.AddColumn(#"Added Custom", "Custom.1", each if List.ContainsAny([Custom],Text.Split(StationList, " , ")) then "Y" else "N"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom.1",{"Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Text After Delimiter", "Transit Waypoints"}, {"Custom.1", "EU"}})
in
    #"Renamed Columns"

Everything upto step #"Added Custom" is the same as the previous query. Delete the steps after this in the previous query, and add everything from #"Added Custom.1" onwards. This will give you the result.

Kind regards,

Rohit

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

[Anonymous]

Hi!

This is what happends after step 1, when i add  {0}

Do i have to change something related with type of data??

[rohit_singh · Accepted solution]

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

[Anonymous]

Thanks! now is correct.

My only problem is that now thats too long to refresh. Is this normal? 

[HotChilli]

You can split by delimiter in Power Query to get the first part of your question.

Once by "-" as far left as possible.

Then Split the resulting column again, by "-" as far right as possible.

--

I don't understand what you are asking in the 2nd part of the question.  What are you trying to match up with?  Maybe you can provide a data sample?

[Anonymous]

hmm, i will need to look for " delimiter" then, i dont know that function. Maybe you can explain me alittle bit more about this. 

Here you have an axemple.

There you have 3 lines of "waypoits"  , then i need to take out the first one and the last one.

Then if you see in detal , CDGRT is the only station in EU, so what I need is to : if there is any waypoint that belong to EU, should be tag as Y in column " EU trasnit" 

Hope I was clear enough.

Thansk!!

[rohit_singh · Accepted solution]

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! 🙂

[Anonymous]

This worked perfect!! the only question i have is that now every time I refresh to add more data or somethig it takes too long. Is that normal?  shoud be working these ways? 

Thanks!

[Anonymous]

Hi again...

On the step 7 I am reciving these : 

I see the result from the merge as a table column, dont know why...

[rohit_singh]

Hi @Anonymous ,

You have to expand the table by clicking on the button highlighted below and select the EU LOC ID column

[Anonymous]

Sorry would you mind please help me on the following steps ? 

-Step 3 how to Convert into a comma separated list. I ve been looking how to doit but there is not a clear way online.

-Step 4 Group by
-Step 6  fuzzy merge.

[rohit_singh]

Hi @Anonymous ,

I already sent you the sample code in my answer yesterday. You just need to copy and paste the M-code for table "Waypoints" into a new blank query and you will be able to see the steps. 

Kind regards,

Rohit

[Anonymous]

well the thing is that i dont have the exact table as I shared , that was only for  example porpuse. So i am checking the code and try to emulate all the steps..

I have the following issue.
Once i do the step 3

then I click on TABLE to witch into LIST

Once i do that an ERROR just apear. Do know how move from there

[rohit_singh]

Hi @Anonymous ,

You need to check the name of the column you're using

Your calculation has the column name listed as "Text After Delimiter" but the name of the column in your case is "Transit waypoints". I think that is causing the error.
Change "Text After Delimiter" to "Transit waypoints" in the code above.

Kind regards

Rohit

[Anonymous]

Thank you very much!! this is amazing....

Let me try all that you did, and will go back to you. 

I will still need to lear how to :
-Step 3 how to Convert into a comma separated list. 

-Step 4 Group by
-Step 6  fuzzy merge.

Let me try everything and i will let you know how it went.

Thanks!