Merging tables by 2 conditions - one by exact and one by the clossest less than or equal to value

[apoje]

HI,

I have two tables which I would like to merge by 2 conditions.

1st condition is simple and it is an exact match - the atributte is a text classification.

2nd condition is a bit tricky: I need the match to be the clossest "less than or equal to" value - here the values are decimal numbers . 

I saw some posts reffering to fuzzy merge - but I think this applies only to text values and I also do not see this option anywhere..

I appreciate the help!

Andraz

[Jimmy801]

Hello
are there any news on this topic? Did solve or help any reply your problem?
If this is the case, please mark it as solution.

Jimmy

[Anonymous]

As you suspect, fuzzy merge does not apply to numbers. The code below may be what you are looking for.

First table_items

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_items"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"sku", type text}, {"item-package-weight", type number}, {"unit-of-weight", type text}, {"Package-Classification", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Package-Classification"},tbl_weight,{"ParcelType"},"tbl_weight",JoinKind.LeftOuter),
    AddFilterFirst = Table.AddColumn(#"Merged Queries", "Custom", each Table.First(Table.SelectRows([tbl_weight],
(row) => [#"item-package-weight"] <= 
row[Weight]))),
    #"Expanded Custom" = Table.ExpandRecordColumn(AddFilterFirst, "Custom", {"Marketplace", "Price"}, {"Marketplace", "Price"})
in
    #"Expanded Custom"

Now table_weight

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_weight"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ParcelType", type text}, {"Weight", Int64.Type}, {"Marketplace", type text}, {"Price", type number}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ParcelType", Order.Ascending}, {"Weight", Order.Ascending}}),
    Buffer = Table.Buffer(#"Sorted Rows")
in
    Buffer

[dax]

Hi apoje,

Yes, if possible, could you please inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[apoje]

Hi @dax  and @Mariusz ,

the data is below: 

https://ipotechnik-my.sharepoint.com/:x:/g/personal/andraz_poje_ipotechnik_de/EVBZG6g5XylEpfc3pRLLeP...

Tables I want to match are in seperate sheets and in PQ are already matched by ParcelType criteria, which is an exact one. Now I would like to expand only the rows that match the most by weight - in order to thet the shipping price of the parcel

Thank you!

Andraz

[Jimmy801 · Accepted solution]

Hello @apoje 

try to change your query tbl_weight like this

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_weight"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ParcelType", type text}, {"Weight", Int64.Type}, {"Marketplace", type text}, {"Price", type number}}),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn
    (
    #"Changed Type", 
    "Benutzerdefiniert", 
    each Table.First(Table.Sort(
    Table.SelectRows
    (
    tbl_items, 
    (select)=> select[#"Package-Classification"] = [ParcelType] and select[#"item-package-weight"]<= [Weight]
    ),
    {{"item-package-weight", Order.Ascending}}))
    ),
    #"Erweiterte Benutzerdefiniert" = Table.ExpandRecordColumn(#"Hinzugefügte benutzerdefinierte Spalte", "Benutzerdefiniert", {"sku", "item-package-weight", "unit-of-weight", "Package-Classification"}, {"Benutzerdefiniert.sku", "Benutzerdefiniert.item-package-weight", "Benutzerdefiniert.unit-of-weight", "Benutzerdefiniert.Package-Classification"})
in
    #"Erweiterte Benutzerdefiniert"

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[apoje]

also tried the following:

tbl-fba-fees is tbl-weight in the sample data and the column weight<=(g) is weight 

everything else is the same

[Jimmy801]

Hello @apoje 

as you have already tried, you have to go for the add.column where you execute a selectrows. In the third parameter you have to write something like this

each [ColumnForExactMatch]= "Value to be filtered" and [ColumnA]<= [ColumnB]

However it will return everything that matches the criterium, not only the closest. This could be achieved by doing a transformColumns ans selecting from the table found only the closest.

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[Mariusz]

Hi @apoje 

Can you provide some data samples?

Many Thanks

Mariusz