Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
1 year ago
Solved

Change Column value from Current Value/Name

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

5 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    1 year ago

    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's avatar
      AllanBerces
      Post Prodigy
      1 year ago

      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's avatar
        Ritaf1983
        Super User
        1 year ago

        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.

  • AllanBerces's avatar
    AllanBerces
    Post Prodigy
    1 year ago

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

     

    Thank you

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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.