Forum Discussion
Solved
Repeat rows until next day
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 |
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.
2 Replies
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
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.
