Matrix Date

[Simon_Br]

Hello all the Power BI Specialits,

i have a question to Power Query.

I have a table incl. a Date from and to. But i used every Date and not only the range.

Per Example:

the Table:

Modell	from	to
A	12.11.2023	15.11.2023
B	31.12.2023	31.12.2023
C	15.04.2024	05.06.2024

the table as i need it:

	Modell
	A	B	C
12.11.2023	X
13.11.2023	X
14.11.2023	X
15.11.2023	X
16.11.2023
17.11.2023
18.11.2023
30.12.2023
31.12.2023		X
01.01.2024
02.01.2024
14.04.2024
15.04.2024			X
16.04.2024			X
17.04.2024			X
…
05.06.2024			X

Its ties possible and if yes, we can i do that?

BG

Simon

[BITomS · Accepted solution]

Hi @Simon_Br ,

In the first instance, it sounds like you want to structure your fact table with help from the following: https://community.fabric.microsoft.com/t5/Desktop/Tutorial-Fill-All-Dates-Between-Start-Date-and-End...

This would give you a single date column that you could then join to your date dimension table (assuming you have one) which would allow you to visualise what you need via a matrix.

Hope that helps!

[Omid_Motamedise · Accepted solution]

See this formula

let
    Source = Table.FromRows({{"A",	#date(2023,11,12),	#date(2023,11,15)},
{"B", 	#date(2023,12,31),#date(2023,12,31)},
{"C",	#date(2024,04,15),	#date(2024,6,5)}},{"Modell", "from", "to"}),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Dates([from],Duration.Days([to]-[from])+1,#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Modell]), "Modell", "to", List.Count),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"from"})
in
    #"Removed Columns"

For more advance solutions, see the link of similar challnge on my linkedin page as below

https://www.linkedin.com/posts/omid-motamedisedeh-74aba166_excelchallenge-powerquerychallenge-excel-...

[BITomS · Accepted solution]

Hi @Simon_Br ,

In the first instance, it sounds like you want to structure your fact table with help from the following: https://community.fabric.microsoft.com/t5/Desktop/Tutorial-Fill-All-Dates-Between-Start-Date-and-End...

This would give you a single date column that you could then join to your date dimension table (assuming you have one) which would allow you to visualise what you need via a matrix.

Hope that helps!