Extracting product dimensions from several collumns

[Anonymous]

Dear community,

I have a list of products:

...and a table that relates products to product groups:

"I.e.: product numbers from 1000 to 1999 belong to product group A"

...which I want to combine to a table that shows the products and their product groups:

I tryed different combinations of CALCULATE, LOOKUPTABLE, FILTER and SELCTEDVALUE, but didn't come to a result.

I'd appreciate your help a lot!

Many thanks

Daniel

[Pragati11]

Hi @Anonymous ,

i have got a workaround to achieve this.

1. I have following tables as follows:
2. In Query Editor I did following tranasformations on Product table with just Product column in it:
   • Duplicated Product column (Ignore name I got it wrong in the table but this is the table with just "Product" column containing Product values as in your first table. I have renamed it in the end.)
   • Changed the duplicated column type to TEXT and Extracted 1st character from this column:
   • Again modified datatype of this duplicated column to WHOLE NUMBER & renamed it to "lowerVal"
   • Added a custom column as shown below: 
   • Added another custom column using this FROM column as follows:  so finally we end with FROM and TILL column in Product table. Just removing "lowerVal" column and renaming Product Group coulmn to Product we get: 
3. Now you easily retrieve Product Group against these Products from the ProductGroup table.
4. Create a relationship between 2 tables on FROM column.
5. then just drag columns as follows and you end up with Product Group against Product value:

You finally get the required result.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[Anonymous]

Dear @Pragati11 ,

thanks for your idea and the great description!

Actually your approach is the work arround I am currently using. The problem is that in the future more product groups will arrise like 1000<AA>1500. So I'd like to use an approach that somehow looks like this:

Productgr = IF(Products[Product] >Productgroups[from] && Products[Product] < Productgroups[till]); Productgroups[ProductGroup])

Any idea 😅?

Regards

Daniel

[Anonymous]

Paste both pieces into PQ:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagkkzMGkOJi3ApCWYNDRQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ProductID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProductID", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Groups", each Groups),
    #"Expanded Groups" = Table.ExpandTableColumn(#"Added Custom", "Groups", {"Group", "From", "To"}, {"Group", "From", "To"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Groups", "Should Retain", each [From] <= [ProductID] and [ProductID] <= [To]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Should Retain] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"From", "To", "Should Retain"})
in
    #"Removed Columns"

and

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIAYmOlWJ1oJScgywSIzcA8ZyDLHIgtwTwXMEtHydBIKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Group = _t, From = _t, To = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"From", Int64.Type}, {"To", Int64.Type}})
in
    #"Changed Type"

Works on condition that all ProductID's do indeed belong to one group. If there's no group that the ProductID belongs to, it'll be filtered out. But you can easily adjust the code so that if there's no group for it, null is assigned.

Best

D

[Anonymous · Accepted solution]

And this code works for any arrangement. One condition is that the groups should not overlap in terms of product ids they cover. If you have something like this, you'll get an error.

File attached.

Best

D

[Anonymous]

Impressive! Thank you very much!

[Mariusz]

Hi @Anonymous 

You can Merge Queries in Power Query

https://www.youtube.com/watch?v=dTdFt9AvDHA&t=89s

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hi @Mariusz,

I appreciate your immediate help!

You suggested to merge the queries. I would love to do so but merging queries works usually with common collumns.

In my case I need something like:
Productgr = IF(Products[Product] >Productgroups[from] && Products[Product] < Productgroups[till]); Productgroups[ProductGroup])

Any ideas how to solve that?

Regards

Daniel