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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.