Forum Discussion
Infilling Missing data
Hi LCrossling
This can also be done in DAX, and it would perhaps be simpler. Place the following M code in a blank query to see the steps of the transformation in Power Query. It can be done in a more compact way but I have used simpler steps on purpose so that it is clearer.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc+7DcAgDADRVSLXSID5KbNEFMH775ActEBxcvFki+eREH24vQYN4uTixX94ySAWpbsdUxgZxPTAEowMYmkx3R7NMGL5wOa2AiNWFkvbbRVGrB7Y/EKDEWvS+wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Provider = _t, A = _t, B = _t, C = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Provider", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Providers", each [Provider], type table [Date=nullable date, Provider=nullable number, A=nullable text, B=nullable text, C=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Missing", each List.RemoveMatchingItems(List.Distinct(#"Changed Type"[Provider]),[Providers])), #"Expanded Missing" = Table.ExpandListColumn(#"Added Custom", "Missing"), #"Filtered Rows" = Table.SelectRows(#"Expanded Missing", each ([Missing] <> null)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Providers"}), #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Previous date", each List.Max(Table.SelectRows(#"Changed Type", (inner)=> [Missing]=inner[Provider] and inner[Date] <[Date])[Date])), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Missing rows", each Table.SelectRows(#"Changed Type", (inner)=> inner[Date]=[Previous date] and inner[Provider] = [Missing] ) ), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Missing", "Previous date"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Date", "Actual Date"}}), #"Expanded Missing rows" = Table.ExpandTableColumn(#"Renamed Columns", "Missing rows", {"Date", "Provider", "A", "B", "C"}, {"Date", "Provider", "A", "B", "C"}), missingRowsT = Table.TransformColumnTypes(#"Expanded Missing rows",{{"Date", type date}, {"Provider", Int64.Type}, {"A", type text}, {"B", type text}, {"C", type text}}), #"Removed Columns2" = Table.RemoveColumns(missingRowsT,{"Date"}), #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns2",{{"Actual Date", "Date"}}), res= Table.Combine({#"Changed Type", #"Renamed Columns1"}), #"Sorted Rows" = Table.Sort(res,{{"Date", Order.Ascending}, {"Provider", Order.Ascending}}) in #"Sorted Rows"Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi LCrossling
This can also be done in DAX, and it would perhaps be simpler. Place the following M code in a blank query to see the steps of the transformation in Power Query. It can be done in a more compact way but I have used simpler steps on purpose so that it is clearer.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc+7DcAgDADRVSLXSID5KbNEFMH775ActEBxcvFki+eREH24vQYN4uTixX94ySAWpbsdUxgZxPTAEowMYmkx3R7NMGL5wOa2AiNWFkvbbRVGrB7Y/EKDEWvS+wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Provider = _t, A = _t, B = _t, C = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Provider", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Providers", each [Provider], type table [Date=nullable date, Provider=nullable number, A=nullable text, B=nullable text, C=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Missing", each List.RemoveMatchingItems(List.Distinct(#"Changed Type"[Provider]),[Providers])),
#"Expanded Missing" = Table.ExpandListColumn(#"Added Custom", "Missing"),
#"Filtered Rows" = Table.SelectRows(#"Expanded Missing", each ([Missing] <> null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Providers"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "Previous date", each List.Max(Table.SelectRows(#"Changed Type", (inner)=> [Missing]=inner[Provider] and inner[Date] <[Date])[Date])),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Missing rows", each Table.SelectRows(#"Changed Type", (inner)=> inner[Date]=[Previous date] and inner[Provider] = [Missing] ) ),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Missing", "Previous date"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Date", "Actual Date"}}),
#"Expanded Missing rows" = Table.ExpandTableColumn(#"Renamed Columns", "Missing rows", {"Date", "Provider", "A", "B", "C"}, {"Date", "Provider", "A", "B", "C"}),
missingRowsT = Table.TransformColumnTypes(#"Expanded Missing rows",{{"Date", type date}, {"Provider", Int64.Type}, {"A", type text}, {"B", type text}, {"C", type text}}),
#"Removed Columns2" = Table.RemoveColumns(missingRowsT,{"Date"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns2",{{"Actual Date", "Date"}}),
res= Table.Combine({#"Changed Type", #"Renamed Columns1"}),
#"Sorted Rows" = Table.Sort(res,{{"Date", Order.Ascending}, {"Provider", Order.Ascending}})
in
#"Sorted Rows"
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Thanks for this seems to work but it is running very slowly on my huge dataset. What would be the method you would use in DAX?
Apologies, I forgot to answer earlier. Try creating a new calculated table in DAX, where Table1 is your initial table:
Table1_filled = VAR t1_ = CROSSJOIN ( DISTINCT ( Table1[Date] ), DISTINCT ( Table1[Provider] ) ) VAR t2_ = SUMMARIZE ( Table1, Table1[Date], Table1[Provider] ) VAR filledRowsT_ = GENERATE ( EXCEPT ( t1_, t2_ ), VAR previousDate_ = CALCULATE ( MAX ( Table1[Date] ), Table1[Date] < EARLIER ( Table1[Date] ) ) RETURN CALCULATETABLE ( ROW ( "A", DISTINCT ( Table1[A] ), "B", DISTINCT ( Table1[B] ), "C", DISTINCT ( Table1[C] ) ), Table1[Date] = previousDate_ ) ) RETURN UNION ( Table1, filledRowsT_ )If you can share your real data (perhaps anonymized if necessary) I can try to optimize it, either in DAX or PQ
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
