Generate Series Table for Every Value in Other Dimension

[AverageBiUser]

Hello,

I am looking to create a table which details every date between two time stamps, for every ID present in the table. Where, first date has code of "006" & last date has code of "014"

Preferably, this table would be filtered to show dates only in 2023. 

Existing table structure:

ID	Code	Date
337448822	006	21/10/2023
337448822	009	28/11/2023
475933872	006	13/06/2023
475933872	009	14/07/2023
287922839	006	12/10/2022
287922839	009	15/02/2023

Desired new table structure:

Date	ID
21/10/2023	337448822
22/10/2023	337448822
23/10/2023	337448822
24/10/2023	337448822
…	337448822
28/11/2023	337448822
13/06/2023	475933872
14/06/2023	475933872
15/06/2023	475933872
16/06/2023	475933872
….	475933872
14/07/2023	475933872

Any messages would be greatly appreciated

[Anonymous · Accepted solution]

Hi @AverageBiUser ,

Please follow these steps:

1.        group by

2.        added custom column

List.Dates([min_date],Duration.Days([max_date]-[min_date])+1,#duration(1,0,0,0))

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc6xDcAgDETRVZBrJNtnwGaWiP3XCKFIRJLmqqevOw4y81IigESZRNpcCIxVGEojf0S/hTJiieK1m4VjT0hjtR2kV0OctSyC8A6E9e0Hrh+KP/E0Jqo0xgk=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Code = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Code", Int64.Type}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"max_date", each List.Max([Date]), type nullable date}, {"min_date", each List.Min([Date]), type nullable date}, {"max_code", each List.Max([Code]), type nullable number}, {"min_code", each List.Min([Code]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Dates([min_date],Duration.Days([max_date]-[min_date])+1,#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"max_code", "min_code", "max_date", "min_date"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "Custom", "Custom - Copy"),
    #"Extracted Year" = Table.TransformColumns(#"Duplicated Column",{{"Custom - Copy", Date.Year, Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Extracted Year", each ([#"Custom - Copy"] = 2023)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Custom - Copy"})
in
    #"Removed Columns1"

3.        Final output

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

^{Best Regards,}

^{Yifan Wang}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[AverageBiUser]

ID	Code	Date
337448822	006	21/10/2023
337448822	009	28/11/2023
475933872	006	13/06/2023
475933872	009	14/07/2023
287922839	006	12/10/2022
287922839	009	15/02/2023

Date	ID
21/10/2023	337448822
22/10/2023	337448822
23/10/2023	337448822
24/10/2023	337448822
…	337448822
28/11/2023	337448822
13/06/2023	475933872
14/06/2023	475933872
15/06/2023	475933872
16/06/2023	475933872
….	475933872
14/07/2023	475933872

Tables above (hopefully with clearer format)