Merge not matching

[Anonymous]

Hi!

I have he following question.

I am trying to execut a fuzzy merge in betwen 2 tables : 

My experctation would be to receive on  "FACT -IR_IM raw data"  table a colum that say "Y" for every point find in colum "Custom" from "DIM-LOCIDS" .

But seems not be working porperly since it puts "Y" on every shipment.

@rohit_singh  

Hope to find the solution 🙂

Thanks!

[rohit_singh · Accepted solution]

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