Merge and Collate Data correctly

[dansta87]

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

[dansta87]

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?

[Ahmedx]

show me what your table looks like so I can understand what you need

[dansta87]

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?

[Ahmedx]

But if each ITEM is added as its own column
why do you have them separately and how is it I don’t understand

[dansta87]

Sorry.

Following your video.

Pivoting on the ITEM column adds each ITEM as its own column into the table.

[Ahmedx]

try this

[Ahmedx]

this is so you can filter by date

[dansta87]

Thank You.

Using both your solutions i've managed to get the data to display how i require.

I appreciate your help.

[Ahmedx]

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

[Ahmedx · Accepted solution]

to know how to do this watch my video and  pbix File

List.Generate(()=>[DATEUSED],
(x)=> x<=[DATERETURNED],
(x)=> Date.AddDays(x,1))

 https://1drv.ms/v/s!AiUZ0Ws7G26RjHQb92gyVK8szXUS?e=E96kJl