Forum Discussion
Merge and Collate Data correctly
Hi,
I am fairly new to PowerBI and ive been struggling to try and get the correct data, i feel like i am going in the right direction but i need some assistance.
I have a Data Query that pulls the following data from an SQL Server:
| ITEM | DATEUSED | DATERETURNED | USEDBY |
| ITEM 1 | 2024-01-01 | 2024-01-02 | PERSON1 |
| ITEM 2 | 2024-01-03 | 2024-01-10 | PERSON2 |
| ITEM 3 | 2022-10-01 | 2023-10-01 | PERSON3 |
I also have a custom CALENDARAUTO table that i use for date selecting via a slicer.
I would like to be able to select a day or range of days using the slicer and then show when an item has or hasnt been used over that period, and if a particular item has not been used then i would like it to be shown with no data.
(DATEUSED and DATERETURNED are inclusive).
e.g. Output (if i select the date range 2024-01-01 - 2024-01-03)
| DATE | ITEM1 | ITEM2 | ITEM3 |
| 2024-01-01 | PERSON 1 | ||
| 2024-01-02 | PERSON 1 | ||
| 2024-01-03 (and so on) | PERSON 2 |
I have tried going down the path of a merge query to merge the calendar table with the data table to get a combined table but it is only showing rows where data exists.
I dont know if what i wasnt is even possible, please can someone assist?
Thanks
to know how to do this watch my video and pbix File
List.Generate(()=>[DATEUSED], (x)=> x<=[DATERETURNED], (x)=> Date.AddDays(x,1))
10 Replies
to know how to do this watch my video and pbix File
List.Generate(()=>[DATEUSED], (x)=> x<=[DATERETURNED], (x)=> Date.AddDays(x,1))This is amazing, thank you.
I've come across a few potential issues with this method though.
There are a total of around 600+ items, meaning that each item needs to be manually added to the table as a column, and new items will also have to be manually added.
- Ive tried merging these into their own table so it can be used for filtering etc, however again each column needs to be typed manually to merge?
show me what your table looks like so I can understand what you need
Thank You.
This is essentially the output table i am trying to get:
With the ability to filter on the item.
But if each ITEM is added as its own column, would i need to aggregate all of these columns into a single column so that i can filter them?
