Forum Discussion
Power Query - Create new table and auto populate on a schedule.
Hi.
I am trying to create a new table with the details that I will explain below. I'm firstly looking to find out if it is possible within Power BI.
My client is using Business Central in their warehouse and have Bin locations.
In Power BI we would like to report on the Number of bins that are in use and count how many are in use every day.
As the items come in and out of the warehouse, the bins will fluctuate between in use or empty and we would like to count at the end of the day (and every other day) how many bins are "not empty" and visualise on a line graph to show the fluctuation day by day.
I have a Bins table, but it only has the empty status of now (which is relevant at the end of the day). and I have the entries table that shows all of the movements of stock.
I think I need to create a new table, with one row per day that is calculated and entered to show how many bins are not empty at that stage based on the entries table that we have.
The next day a new line would be added with a new count.
I can add details if relevant, but I think that is a question based on if it is possible!
thanks in advance,
Nick
Hi NicholasRoseCiT ,
Based on your screenshot, I see that your datasheet contains date columns, if there is a one-to-one or one-to-many relationship between the Bins table and the Entries table (don't have a many-to-many, if it is a many-to-many please add a bridge table to split the many-to-many into many-to-one and one-to-many), you can try the following:
Use this DAX to create a new table(Please customize the start date):Count_of_NullEmpty = CALENDAR(DATE(2024, 1, 1), TODAY())Then use this DAX to create a new column in this table:
Value = CALCULATE( COUNTROWS('Table'), 'Table'[empty] = "False", RELATEDTABLE('Table') )The final output is as below:
Because the calculation columns and tables do not dynamically change as the data changes, you only need to manually click Refresh after adding new data each day, and the Empty = "False" records for that day will be updated.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi Amit
I'm not sure that is what I need.
I have a Bins table,m which is correct as at that period of time.And I have an entries table which is the transactions behind this bins table
I need to capture once per day the number of bins as a count from the bin table that have empty as False. a Line per day, per warehouse class code.
Because I want to show this as a line graph visual, the entries table doesnt have enough entries to fill in the gaps between the date of receipt and shipment. And the bins table is only the current status.
is that more helpful?Hi NicholasRoseCiT ,
Based on your screenshot, I see that your datasheet contains date columns, if there is a one-to-one or one-to-many relationship between the Bins table and the Entries table (don't have a many-to-many, if it is a many-to-many please add a bridge table to split the many-to-many into many-to-one and one-to-many), you can try the following:
Use this DAX to create a new table(Please customize the start date):Count_of_NullEmpty = CALENDAR(DATE(2024, 1, 1), TODAY())Then use this DAX to create a new column in this table:
Value = CALCULATE( COUNTROWS('Table'), 'Table'[empty] = "False", RELATEDTABLE('Table') )The final output is as below:
Because the calculation columns and tables do not dynamically change as the data changes, you only need to manually click Refresh after adding new data each day, and the Empty = "False" records for that day will be updated.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Dino.
I have got this working to a degree, but its changing all the previous dates to the same.Suspect I have some slight errors in this DAX.
Im finding that it updates all dates to the current value.
This was 5 on the 10th and 6 on the 11th,
I have the following relationship between the tables.
Regards
Nick
Hi Dino.
I think this will do it.
Thank you very much for this! Appreciate everyones time here.
Nick
