Repeat rows until next day

[Felipekard]

Hi guys, can you help me?

I has this table:

COUNTRY	DATE	VALUE
BRAZIL	09/20/2023	10
BRAZIL	09/21/2023	10
BRAZIL	09/22/2023	10
CANADA	09/20/2023	10
CANADA	09/23/2023	20
CANADA	09/25/2023	30

And Brazil is daily, but canada only have some days.

I need made canada daily, repeat the next days with the value of last days, create the red rows below.

my table need be like this:

COUNTRY	DATE	VALUE
BRAZIL	09/20/2023	10
BRAZIL	09/21/2023	10
BRAZIL	09/22/2023	10
CANADA	09/20/2023	10
CANADA	09/21/2023	10
CANADA	09/22/2023	10
CANADA	09/23/2023	20
CANADA	09/24/2023	20
CANADA	09/25/2023	30

[v-xinruzhu-msft · Accepted solution]

Hi @Felipekard 

You can create two blank queries in power query 

then put the following code to advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgpyjPL0UdJRMrDUNzIAIiNjIMfQQClWB03SEJ+kEZqks6Ofo4sjDmNRJI1hkkZYJE1hksZAyVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [COUNTRY = _t, DATE = _t, VALUE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"COUNTRY", type text}, {"DATE", type date}, {"VALUE", Int64.Type}})
in
    #"Changed Type"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgpyjPL0UdJRMrDUNzIAIiNjIMfQQClWB03SEJ+kEZqks6Ofo4sjDmNRJI1hkkZYJE1hksZAyVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [COUNTRY = _t, DATE = _t, VALUE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"COUNTRY", type text}, {"DATE", type date}, {"VALUE", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"COUNTRY", Order.Ascending}, {"DATE", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"COUNTRY"}, {{"min", each List.Min([DATE])},{"max",each List.Max([DATE])}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Dates([min],Duration.Days([max]-[min])+1,#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"min", "max"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"COUNTRY", "Custom"}, #"Query1", {"COUNTRY", "DATE"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"VALUE"}, {"VALUE"}),
    #"Filled Down" = Table.FillDown(#"Expanded Table (2)",{"VALUE"})
in
    #"Filled Down"

Output

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.

[slorin]

Hi,

let
Source = YourSource,
#"Grouped Rows" = Table.Group(Source, {"COUNTRY"},
   {{"DATE", each List.Dates(List.Min([DATE]), Duration.Days(List.Max([DATE])-List.Min([DATE]))+1, #duration(1,0,0,0))}}),
#"Expanded {0}" = Table.ExpandListColumn(#"Grouped Rows", "DATE"),
#"Merged Queries" = Table.NestedJoin(#"Expanded {0}", {"COUNTRY", "DATE"}, Source, {"COUNTRY", "DATE"}, "Expanded {0}", JoinKind.LeftOuter),
#"Expanded {0}1" = Table.ExpandTableColumn(#"Merged Queries", "Expanded {0}", {"VALUE"}, {"VALUE"}),
#"Filled Down" = Table.FillDown(#"Expanded {0}1",{"VALUE"})
in
#"Filled Down"

Stéphane