Is there a way to achieve this transformation using PowerQuery?
Input table:
| City Year | Month | Value |
|---|---|---|
| London | ||
| 2016 | January | 19 |
| February | 20 | |
| March | 17 | |
| April | 20 | |
| 2017 | January | 19 |
| February | 20 | |
| March | 17 | |
| April | 20 | |
| Paris | ||
| 2016 | January | 19 |
| February | 20 | |
| March | 17 | |
| April | 20 | |
| 2017 | January | 19 |
| February | 20 | |
| March | 17 | |
| April | 20 | |
| Rome | ||
| 2016 | January | 19 |
| February | 20 | |
| March | 17 | |
| April | 20 | |
| 2017 | January | 19 |
| February | 20 | |
| March | 17 | |
| April | 20 |
Output:
| City | Month/Year | Value |
|---|---|---|
| London | January 2016 | 19 |
| London | February 2016 | 20 |
| London | March 2016 | 17 |
| London | April 2016 | 20 |
| London | January 2017 | 19 |
| London | February 2017 | 20 |
| London | March 2017 | 17 |
| London | April 2017 | 20 |
| Paris | January 2016 | 19 |
| Paris | February 2016 | 20 |
| Paris | March 2016 | 17 |
| Paris | April 2016 | 20 |
| Paris | January 2017 | 19 |
| Paris | February 2017 | 20 |
| Paris | March 2017 | 17 |
| Paris | April 2017 | 20 |
| Rome | January 2016 | 19 |
| Rome | February 2016 | 20 |
| Rome | March 2016 | 17 |
| Rome | April 2016 | 20 |
| Rome | January 2017 | 19 |
| Rome | February 2017 | 20 |
| Rome | March 2017 | 17 |
| Rome | April 2017 | 20 |
Here is a brute force way of doing it with your sample data schema:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8snPS8nPU9JRAqFYnWglIwNDMyDbKzGvNLGoEsgytASLA1luqUlFUEEjA5igb2JRcgZImTlMxLGgKDMHoQZooDlVDQxILMosHlIuDsrPTR06Do4FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"City Year" = _t, Month = _t, Value = _t]),
#"Added Custom" = Table.AddColumn(Source, "City", each if [Month] = "" then [City Year] else null, type text),
#"Filled Down" = Table.FillDown(#"Added Custom",{"City"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Month] <> "")),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","",null,Replacer.ReplaceValue,{"City Year"}),
#"Filled Down1" = Table.FillDown(#"Replaced Value",{"City Year"}),
#"Inserted Merged Column" = Table.AddColumn(#"Filled Down1", "Month/Year", each Text.Combine({[Month], [City Year]}, " "), type text),
#"Removed Other Columns" = Table.SelectColumns(#"Inserted Merged Column",{"City", "Month/Year", "Value"})
in
#"Removed Other Columns"