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
aerizk
Helper II
Helper II

Fill missing sequence of rows

Hello,

 

I have a simple table with the following format. each row represents a point in time, so month and plan can be used to create a date column.

MonthPlanCustomerSales
M01Plan_2022CustA100
M02Plan_2022CustA150
M03Plan_2022CustA120
M04Plan_2022CustA320
M05Plan_2022CustA100
M06Plan_2022CustA150
M07Plan_2022CustA200
M08Plan_2022CustA500
M09Plan_2022CustA100
M10Plan_2022CustA100
M11Plan_2022CustA100
M12Plan_2022CustA100
M01Plan_2023CustA100
M02Plan_2023CustA150
M03Plan_2023CustA120
M01Plan_2022CustB100
M02Plan_2022CustB150
M03Plan_2022CustB120
M05Plan_2022CustB100
M06Plan_2022CustB150
M07Plan_2022CustB200
M08Plan_2022CustB500
M09Plan_2022CustB100
M10Plan_2022CustB100
M12Plan_2022CustB100
M01Plan_2023CustB100
M02Plan_2023CustB150

 

For CustA the sequence is complete from January 2022 to March 2023. The problem is for the second customer CustB, there are many missing rows for example M03 in Plan_2023 and M04 & M11 in Plan_2022. So basically I want to complete the sequence of dates and fill the rows missing with the key which in this case in CustB and for the measure it will be nulls. what is the best way to do this in Power Query? 

 

The result would be something like this: the added rows are highlighted in bold.

MonthPlanCustomerSales
M01Plan_2022CustA100
M02Plan_2022CustA150
M03Plan_2022CustA120
M04Plan_2022CustA320
M05Plan_2022CustA100
M06Plan_2022CustA150
M07Plan_2022CustA200
M08Plan_2022CustA500
M09Plan_2022CustA100
M10Plan_2022CustA100
M11Plan_2022CustA100
M12Plan_2022CustA100
M01Plan_2023CustA100
M02Plan_2023CustA150
M03Plan_2023CustA120
M01Plan_2022CustB100
M02Plan_2022CustB150
M03Plan_2022CustB120
M04Plan_2022CustB 
M05Plan_2022CustB100
M06Plan_2022CustB150
M07Plan_2022CustB200
M08Plan_2022CustB500
M09Plan_2022CustB100
M10Plan_2022CustB100
M11Plan_2022CustB 
M12Plan_2022CustB100
M01Plan_2023CustB100
M02Plan_2023CustB150
M03Plan_2023CustB 
2 ACCEPTED SOLUTIONS
rubayatyasmin
Super User
Super User

Hi, @aerizk 

 

I tried using your sample data. I got the required result. 

 

Refer to the ss

rubayatyasmin_0-1696837648275.png

 

download the file that has the solution. 

https://file.io/tauFexFl0dsB

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


View solution in original post

v-xinruzhu-msft
Community Support
Community Support

Hi @aerizk 

You can put the following code to advanced editor in power query

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZGxDsIwDET/JXMHxyYURsxcib2qqu5VF+D/KUOiGtnclAxPucu9cUwD5dSlx7psMxPzfr+/n6/bfmaiNHVfgiOiVEIigitxCghpRIE9zrBHHxDc3rgERGnEFfXIBAm4aQ43bT2Obwj0Iu4eEhHspdQeCu0rtK8mxXNrUzy3NsVzq9CtQremh+vWEuEef83Fm8rvb6cP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Plan = _t, Customer = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Plan", type text}, {"Customer", type text}, {"Sales", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Plan", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Plan.1", "Plan.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Plan.1", type text}, {"Plan.2", Int64.Type}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type1", "Month", Splitter.SplitTextByPositions({0, 1}, false), {"Month.1", "Month.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"Month.2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom", each if [Customer]="CustB" and [Plan.2]=2022 then List.Numbers(1,12) else if [Customer]="CustB" and [Plan.2]=2023 then List.Numbers(1,3) else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Month.1", "Month.2", "Plan.1", "Sales"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Customer] = "CustB")),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Plan.2", "Customer"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Duplicates", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each Table.SelectRows(#"Added Custom",(x)=>x[Plan.2]=[Plan.2] and x[Customer]=[Customer] and  x[Month.2]=[Custom])),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"Month.1", "Plan.1", "Sales"}, {"Month.1", "Plan.1", "Sales"}),
    #"Filled Down" = Table.FillDown(#"Expanded Custom.1",{"Month.1", "Plan.1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"Plan.2", "Customer", "Month.1", "Custom", "Plan.1", "Sales"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns", {{"Custom", type text}}, "en-US"),{"Month.1", "Custom"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Reordered Columns1" = Table.ReorderColumns(#"Merged Columns",{"Customer", "Merged", "Plan.1", "Plan.2", "Sales"}),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns1", {{"Plan.2", type text}}, "en-US"),{"Plan.1", "Plan.2"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Merged.1"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns1",{{"Merged", "Month"}, {"Merged.1", "Plan"}}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Renamed Columns",{"Month", "Plan", "Customer", "Sales"}),
    #"Appended Query" = Table.Combine({#"Reordered Columns2", Table.SelectRows(#"Changed Type",(x)=>x[Customer]="CustA")})
in
    #"Appended Query"

 

 

Best Regards!

Yolo Zhu

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-xinruzhu-msft
Community Support
Community Support

Hi @aerizk 

You can put the following code to advanced editor in power query

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZGxDsIwDET/JXMHxyYURsxcib2qqu5VF+D/KUOiGtnclAxPucu9cUwD5dSlx7psMxPzfr+/n6/bfmaiNHVfgiOiVEIigitxCghpRIE9zrBHHxDc3rgERGnEFfXIBAm4aQ43bT2Obwj0Iu4eEhHspdQeCu0rtK8mxXNrUzy3NsVzq9CtQremh+vWEuEef83Fm8rvb6cP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Plan = _t, Customer = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Plan", type text}, {"Customer", type text}, {"Sales", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Plan", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Plan.1", "Plan.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Plan.1", type text}, {"Plan.2", Int64.Type}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type1", "Month", Splitter.SplitTextByPositions({0, 1}, false), {"Month.1", "Month.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"Month.2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom", each if [Customer]="CustB" and [Plan.2]=2022 then List.Numbers(1,12) else if [Customer]="CustB" and [Plan.2]=2023 then List.Numbers(1,3) else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Month.1", "Month.2", "Plan.1", "Sales"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Customer] = "CustB")),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Plan.2", "Customer"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Duplicates", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each Table.SelectRows(#"Added Custom",(x)=>x[Plan.2]=[Plan.2] and x[Customer]=[Customer] and  x[Month.2]=[Custom])),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"Month.1", "Plan.1", "Sales"}, {"Month.1", "Plan.1", "Sales"}),
    #"Filled Down" = Table.FillDown(#"Expanded Custom.1",{"Month.1", "Plan.1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"Plan.2", "Customer", "Month.1", "Custom", "Plan.1", "Sales"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns", {{"Custom", type text}}, "en-US"),{"Month.1", "Custom"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Reordered Columns1" = Table.ReorderColumns(#"Merged Columns",{"Customer", "Merged", "Plan.1", "Plan.2", "Sales"}),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns1", {{"Plan.2", type text}}, "en-US"),{"Plan.1", "Plan.2"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Merged.1"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns1",{{"Merged", "Month"}, {"Merged.1", "Plan"}}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Renamed Columns",{"Month", "Plan", "Customer", "Sales"}),
    #"Appended Query" = Table.Combine({#"Reordered Columns2", Table.SelectRows(#"Changed Type",(x)=>x[Customer]="CustA")})
in
    #"Appended Query"

 

 

Best Regards!

Yolo Zhu

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

rubayatyasmin
Super User
Super User

Hi, @aerizk 

 

I tried using your sample data. I got the required result. 

 

Refer to the ss

rubayatyasmin_0-1696837648275.png

 

download the file that has the solution. 

https://file.io/tauFexFl0dsB

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


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.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors