how to create new grouping field

[david145]

hi,

i have a data table with 2 fields in the table like the below:

Product	Area
Table	1
Table	3
Table	5
Chair	3
Chair	7
Sink	1
Sink	5
Door	1
etc	etc

each product can have repeating areas as shown above.

i now want to include a new grouping field in my dataset to group certain Areas for each Product. i need to pull in a excel file with the new field named "Position" :

Product	Area	Position
Table	1	1A
Table	3	1A
Table	5	1F
Chair	3	1A
Chair	7	2B
Sink	1	1A
Sink	5	2B
etc	etc	etc

i want to be able to be able to see what "areas" are in what "Position" for each of my "Products".

Ive tried doing a simple Concatenation on my original dataset with my new excel file:

Concatenate(MyData)
load
Product, Area, Position

FROM
[..\..\..\.xlsx]
(ooxml, embedded labels, table is Sheet1);

but im getting all nulls in my Position field.

what is the best way to do this?

thanks

[Or]

Looks like a simple "join"(or "left join", if values may be missing in the second file but included in the first) should work here. If the example is exactly as specified, you don't even need the first table, since the second table contains all of the information from the first plus an additional field.

[JohnSamuel123]

hi @Or  thank you for your reply.

you're right, a simple join would work, however i would then need to drop the "Product" table from my second table so they join correctly. doing this then skews my data because the same Area number for one product may have a different Position value.

so if i just joined them, area 1 would = Position 1A for all my product, whereby i may have a Product that Area 1 would = Position 1F for example.

i need to keep the first data table as as you said some values my be missing in the second file but included in the first.

is there a way to get it working correctly?

thanks 

[Or]

I'm sorry, but I wasn't able to follow that explanation... but if you're looking for an action similar to left join that doesn't impact the original table, try Left Keep.

[JohnSamuel123]

sorry @Or it was a poor explanation.

essentially my table would look like this

Product	Area	Position
Table	1	1A
Table	3	1A
Chair	3	1F
Chair	1	2B

so the 2 products can have the same Area value but different Positions for that value.

using left join would not take this into account

i need to be able have the correct Areas for each Product grouped into the correct Position 

[sidhiq91]

@JohnSamuel123  not sure if I understand your requirement exactly. But a simple join is doing the business for us.

So if you could be more specific with your requirement we can help you out.

[Or]

In this example, no product has two areas with different positions for that area..? Each combination of Product-Area appears to have exactly one line with exactly one position?

[JohnSamuel123]

hi @sidhiq91  @Or ,

so this is the excel file im pulling in and i have filtered it to show just one "Position" 1A: 

you can see that "1A" has different areas for each product.

in my script i then just simply pull in this file and comment our the Product field so i get no synthetic table:

but when i filter the data in the app for Position "1A" and area "1", im getting more products than the 5 that have that area value in my excel file:

what i need is for the app to only show me the 5 products and not every single product in my data

thanks

[Or]

Why are you removing the Product field if this is part of your key? If you just load Area without Product, you'll get all matching areas regardless of product, which doesn't appear to be your desired output...

[JohnSamuel123]

i have to keep the Product field in my original table as i have some mappings and rules around it which would impact the data in the front end.

so i cant remove the Product field in my original table, hence the issue i am facing.

is there a way to get it working correctly while keeping the Product and Area field in my original table?