Dynamic Table Diff

[haleswd258]

I have a Sales Table that lists part numbers that have been purchased.  I also have a Required Stocking Table that contains the part numbers and quantities that we require our customers to stock.  I would like to use Power BI to select a particular customer ,along with other filters.  This would then populate a results table/grid visual with the part numbers and the quantities that the customer would need to purchase in order to be in compliant with our stocking policy. 

I have tried things like Table.RemoveMatchingRows(), but that is just a table, and cannot dynamically change based on selections.  At least to my understanding.  I have also tried making helper columns and filtering using contains() or calculate(counting rows(),filter(),etc... but I just can't get anything to work.  I'm sure it is just my lack of understanding.  Can anyone help me?

I'm sure it goes without saying, but I have greatly simplified the tables for this example.

Sales Table

Part_No	Qty	Customer
100	4	Cust A
200	5	Cust A
300	5	Cust A

Required Stocking Table

Part_No	Qty
100	5
200	5
300	5
400	5

What the Results should look like based on what is shown here.

Part_No	Qty
100	1
400	5

[Anonymous · Accepted solution]

Hi @haleswd258 ,

Here's my solution.

1.Merge two tables.

2.Expand the column.

3.Replace the null in the Qty.1 column with 0

4.Add a custom column to get the stock quantities.

5.Remove the Qty column and the Qty.1 column. Rename the Custom column as the Qty column. You'll get the following.

Filter out the Qty=0.

Best Regards,

Stephen Tao

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

[Vijay_A_Verma]

Solution file uploaded to - https://1drv.ms/x/s!Akd5y6ruJhvhuUyWSf8XMDiJRclu?e=RWqsLW 

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Part_No", Int64.Type}, {"Qty", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Sales[Qty]{List.PositionOf(Sales[Part_No],[Part_No])}),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each [Qty]-[Custom]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Qty", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.1", "Qty"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Qty] <> 0))
in
    #"Filtered Rows"

[haleswd258]

Thank you for this.  So, this is for Excel.  I have been able to pretty much adapt it to Power BI to be able to create a table, but it just creates a table.  If I am in Power BI and I want to filter by customer, or any other field, the table doesn't refresh.  Is there some form of solution using a visual that would make this more functional?

[Vijay_A_Verma]

Excel is used for demonstration as that is the easiest portable way to make you read the query. In place of Excel which is Source line, you can put your own source say any database etc. Is it possible for you to post your pbix file?

[haleswd258]

Sorry, I can't post the pbix.  It has too much proprietary info.  And as I said, I can get it to work in PowerBI the way it is.  I just need something that is more dynamic and can work from a dashboard