Solved: Re: Fill missing sequence of rows

aerizk · ‎10-08-2023

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.

Month	Plan	Customer	Sales
M01	Plan_2022	CustA	100
M02	Plan_2022	CustA	150
M03	Plan_2022	CustA	120
M04	Plan_2022	CustA	320
M05	Plan_2022	CustA	100
M06	Plan_2022	CustA	150
M07	Plan_2022	CustA	200
M08	Plan_2022	CustA	500
M09	Plan_2022	CustA	100
M10	Plan_2022	CustA	100
M11	Plan_2022	CustA	100
M12	Plan_2022	CustA	100
M01	Plan_2023	CustA	100
M02	Plan_2023	CustA	150
M03	Plan_2023	CustA	120
M01	Plan_2022	CustB	100
M02	Plan_2022	CustB	150
M03	Plan_2022	CustB	120
M05	Plan_2022	CustB	100
M06	Plan_2022	CustB	150
M07	Plan_2022	CustB	200
M08	Plan_2022	CustB	500
M09	Plan_2022	CustB	100
M10	Plan_2022	CustB	100
M12	Plan_2022	CustB	100
M01	Plan_2023	CustB	100
M02	Plan_2023	CustB	150

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.

Month	Plan	Customer	Sales
M01	Plan_2022	CustA	100
M02	Plan_2022	CustA	150
M03	Plan_2022	CustA	120
M04	Plan_2022	CustA	320
M05	Plan_2022	CustA	100
M06	Plan_2022	CustA	150
M07	Plan_2022	CustA	200
M08	Plan_2022	CustA	500
M09	Plan_2022	CustA	100
M10	Plan_2022	CustA	100
M11	Plan_2022	CustA	100
M12	Plan_2022	CustA	100
M01	Plan_2023	CustA	100
M02	Plan_2023	CustA	150
M03	Plan_2023	CustA	120
M01	Plan_2022	CustB	100
M02	Plan_2022	CustB	150
M03	Plan_2022	CustB	120
M04	Plan_2022	CustB
M05	Plan_2022	CustB	100
M06	Plan_2022	CustB	150
M07	Plan_2022	CustB	200
M08	Plan_2022	CustB	500
M09	Plan_2022	CustB	100
M10	Plan_2022	CustB	100
M11	Plan_2022	CustB
M12	Plan_2022	CustB	100
M01	Plan_2023	CustB	100
M02	Plan_2023	CustB	150
M03	Plan_2023	CustB

rubayatyasmin · ‎10-09-2023

Hi, @aerizk

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

Refer to the ss

download the file that has the solution.

https://file.io/tauFexFl0dsB

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

View solution in original post

v-xinruzhu-msft · ‎10-09-2023

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.