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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors