Change Column value from Current Value/Name

[AllanBerces]

Hi good day,

Can anyone help me on my table, how can i change the column TA value/name from the current value/name

OUTPUT

Thank you

[AllanBerces]

follow up query if only the TA column is Cancelled and Deferred then change to current value/name otherwise no.

Thank you

[v-rzhou-msft · Accepted solution]

Hi @AllanBerces ,

I suggest you to refer to below M Code to transform your table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdG7DsIwDAXQX0GZi5o4TkNHBBMzW9WBR5gqkPr/Aw3qkCAbW2KypRxdJ84wmNPrurHONOa831pcau7BAraxhWDGhiJWNqCI+WEOl+ctTVO6L/2uYMgzp2NemWZpB+sDcu2wMJ42ClKNYgzQxK8k3x472UQvm6AY1dMmnx3TI83zZ4dQTgOWBZXq/8kqVxmYpMrIxMkk0qTcZPX3jFHEMIuu/uzrTeMb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"JobNo." = _t, TA = _t, #"Progress hrs" = _t, #"Progress Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Progress Date", type date}, {"Progress hrs", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([TA], "TA") then [TA] else null),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"JobNo."}, {{"Count", each _, type table [#"JobNo."=nullable text, TA=nullable text, Progress hrs=nullable number, Progress Date=nullable date, Custom=text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.FillDown([Count],{"Custom"})),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"TA", "Progress hrs", "Progress Date", "Custom", "Index"}, {"TA", "Progress hrs", "Progress Date", "Custom.1", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Count", "Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.1", "Updated TA"}})
in
    #"Renamed Columns"

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ritaf1983]

Hi @AllanBerces 
Please apply these 2 steps in PQ :
1. Add customer column :

if [TA]="Cancelled" or [TA]="Deferred" then null
else [TA]

2. Fill down

Result :

The pbix is attached

If my answer was helpful please give me a Kudos and accept as a Solution.

[AllanBerces]

Hi @Ritaf1983 thank you for the reply, but i think the result is not what i required. the highlighted in yellow should be TA-04

Thank you

[Ritaf1983]

Hi @AllanBerces 
Sorry, I didn't get that it is on the job level.
I corrected the logic, you can download the updated pbix.

If my answer was helpful please give me a Kudos and accept as a Solution.