Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
AverageBiUser
Frequent Visitor

Generate Series Table for Every Value in Other Dimension

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:

 

IDCodeDate
33744882200621/10/2023
33744882200928/11/2023
47593387200613/06/2023
47593387200914/07/2023
28792283900612/10/2022
28792283900915/02/2023


Desired new table structure:

DateID
21/10/2023337448822
22/10/2023337448822
23/10/2023337448822
24/10/2023337448822
337448822
28/11/2023337448822
13/06/2023475933872
14/06/2023475933872
15/06/2023475933872
16/06/2023475933872
….475933872
14/07/2023475933872

 

Any messages would be greatly appreciated

1 ACCEPTED SOLUTION
v-yifanw-msft
Community Support
Community Support

Hi @AverageBiUser ,

Please follow these steps:

  1.        group by

vyifanwmsft_0-1700206773701.png

  1.        added custom column

vyifanwmsft_1-1700206806672.png

 

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"

 

  1.        Final output

vyifanwmsft_2-1700206953623.png

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.

 

View solution in original post

2 REPLIES 2
v-yifanw-msft
Community Support
Community Support

Hi @AverageBiUser ,

Please follow these steps:

  1.        group by

vyifanwmsft_0-1700206773701.png

  1.        added custom column

vyifanwmsft_1-1700206806672.png

 

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"

 

  1.        Final output

vyifanwmsft_2-1700206953623.png

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
Frequent Visitor

ID

CodeDate
337448822 00621/10/2023
337448822 00928/11/2023
47593387200613/06/2023
475933872  00914/07/2023
287922839 00612/10/2022
287922839 00915/02/2023

 

DateID
21/10/2023 337448822
22/10/2023 337448822
23/10/2023337448822
24/10/2023 337448822
337448822
28/11/2023337448822
13/06/2023 475933872
14/06/2023 475933872
15/06/2023 475933872
16/06/2023475933872
….475933872
14/07/2023 475933872



Tables above (hopefully with clearer format)

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.