Forum Discussion
Dynamic Table Diff
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.
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"
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?
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?
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