Is there a way to combine month and year and add city in seperate column transformation using PowerQuery?

Question

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

If the answer below has solved your problem, I invite you to [mark it as accepted](https://stackoverflow.com/help/someone-answers). — Patrick FitzGerald, Sep 12 '21 at 09:53

score 2 · Accepted Answer · answered Jul 30 '21 at 11: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"

Is there a way to combine month and year and add city in seperate column transformation using PowerQuery?

1 Answers1