Null out list of columns' row values based on another column-CodePudding

I have an example table below:

first_name	last_name	country	2019	2020	2021	2022
John	Doe	UK	100	200	null	null
John	Doe	France	150	50	null	100
Mike	Lin	US	null	200	300	400
Anna	How	Germany	null	null	50	90
Anna	How	UK	90	200	800	null

How can I null out the 2019 onward columns to null if the country is UK? The year columns will eventually grow, so I'm trying to adapt for that. The first three columns will not change.

Output:

first_name	last_name	country	2019	2020	2021	2022
John	Doe	UK	null	null	null	null
John	Doe	France	150	50	null	100
Mike	Lin	US	null	200	300	400
Anna	How	Germany	null	null	50	90
Anna	How	UK	null	null	null	null

I was thinking of having List.RemoveItems() to get a list of the year columns. I'm trying to go with each if when doing the conditionals and then using Replacer.ReplaceValue where the value is >0 and replace with null, but not sure how to apply this to the list of columns.

CodePudding user response：

This will account for new years too.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"first_name", type text}, {"last_name", type text}, {"country", type text}, {"2019", Int64.Type}, {"2020", Int64.Type}, {"2021", Int64.Type}, {"2022", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"first_name", "last_name", "country"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if [country] = "UK" then null else [Value]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"first_name", Order.Ascending}, {"last_name", Order.Ascending}, {"Attribute", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Attribute]), "Attribute", "Custom")
in
    #"Pivoted Column"