Show the value X even if the value Y is not exist

[Alexsay]

Hello everyone,
Hello experts,

I have a challenge that has been bothering me for some time.
I have two excel tables:
1. A table with daily stock
2. A table with locations (locations master data from WH)
The table with stocks shows me only the locations that have stock and I try to figure out how it can show me even the locations without stock.
I made a merge between the tables and the common value is the name of the location but the values are doubled and I understand that there must be a match between different values
My tables are:

Stock report (the system extract me only lines with stock, for example the location A-1-0 has a storage capacity for 6 palets but the system extract me a report only with stock)

Locations in table stoc	SSCC (unique code of pallet)
A-1-0	11
A-1-0	22
A-1-0	33

master data of locations. Its 6 lines because the rake has a capacity of six pallets

Code	Room	Area	Used
A-1-0	Drive-In racks	A	Picking
A-1-0	Drive-In racks	A	Picking
A-1-0	Drive-In racks	A	Picking
A-1-0	Drive-In racks	A	Picking
A-1-0	Drive-In racks	A	Picking
A-1-0	Drive-In racks	A	Picking

I want the result to look like this. I want to see the occupancy level of the location and show me how much space is left

Location	SSCC	Room	Area	Used
A-1-0	11	Drive-In racks	A	Picking
A-1-0	22	Drive-In racks	A	Picking
A-1-0	33	Drive-In racks	A	Picking
A-1-0		Drive-In racks	A	Picking
A-1-0		Drive-In racks	A	Picking
A-1-0		Drive-In racks	A	Picking

Any help, advise will be more than welcome
Thank you!

 

[Alexsay]

Anybody?

 

[Alexsay]

pls pls pls

 

[RoryA]

I'd load each table into power query. In each query group on the location code choosing All rows as the aggregation type and add an index column, then expand the data back out again. You can then merge the queries on Location and index number using a Left Outer join to get all location records back and any matching stock ones. So your Stock query will look something like this (assumes a table called Stock):

let
    Source = Excel.CurrentWorkbook(){[Name="Stock"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Locations in table stoc"}, {{"Data", each Table.AddIndexColumn(_, "Index", 1), type table}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Locations in table stoc"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", {"Locations in table stoc", "SSCC (unique code of pallet)", "Index"}, {"Locations in table stoc", "SSCC (unique code of pallet)", "Index"})
in
    #"Expanded Data"

which produces this:

then the Pallets query is similar but with Merge and expand steps:

let
    Source = Excel.CurrentWorkbook(){[Name="Pallets"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Code"}, {{"Data", each Table.AddIndexColumn(_,"Index", 1), type table}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Code"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", {"Code", "Room", "Area", "Used", "Index"}, {"Code", "Room", "Area", "Used", "Index"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Data", {"Code", "Index"}, Stock, {"Locations in table stoc", "Index"}, "Stock", JoinKind.LeftOuter),
    #"Expanded Stock" = Table.ExpandTableColumn(#"Merged Queries", "Stock", {"SSCC (unique code of pallet)"}, {"SSCC (unique code of pallet)"})
in
    #"Expanded Stock"

which results in: