Forum Discussion

Fromit87's avatar
Fromit87
Advocate I
3 years ago
Solved

Change column names based on a condition

Hi there,

 

I have a table to transform to a certain layout. 

The end result should be that any column, that has (text)values in the rows starting with "L" should be renamed to Level 1, Level 2, Level 3 etc. dynamically. I don't mind the names of the other columns.

 

Is there a way of renaming columns based on the condition that the first record (index={0}) of the column starts with "L"?

The reason is, I might have 4 columns today fulfilling the condition, but there might be more or less in the future - and I don't want to adjust the transformation each time...

 

So in the screenshot below it should be renaming Merged.2 to Level 1, Merge.10 to Level 2, etc.

Is there a way?

Thanks!

 

 

Need Help
  • ams1's avatar
    ams1
    3 years ago

    Hi,

     

    GIVEN

    WHEN

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], // YOUR table here
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Merged.1"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "LevelColumnName", each if Text.StartsWith([Value], "L") then [Index] else null),
    OriginalLevelIndices = List.RemoveNulls(Table.Column(#"Added Custom", "LevelColumnName")),
    NewLevelIndices = List.Numbers(1, List.Count(OriginalLevelIndices)),
    TranslationTable = Table.FromColumns({NewLevelIndices,OriginalLevelIndices}),
    Final = List.Accumulate(Table.ToRows(TranslationTable), #"Added Custom", (t,r) => Table.ReplaceValue(t, r{1}, r{0}, Replacer.ReplaceValue, Table.ColumnNames(t))),
    #"Added Custom1" = Table.AddColumn(Final, "NewColumnNames", each if [LevelColumnName] <> null then "Level" & Text.From([LevelColumnName]) else [Attribute]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute", "Index", "LevelColumnName"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[NewColumnNames]), "NewColumnNames", "Value")
in
    #"Pivoted Column"

    THEN the "level" columns should be renamed

     

    Hope this helps.

6 Replies

  • Poohkrd's avatar
    Poohkrd
    Advocate I
    3 years ago

    Hi! Do you need to check only the values in the first row or in the entire column?

    • Fromit87's avatar
      Fromit87
      Advocate I
      3 years ago

      Poohkrd, yes I only need to check the value in the first row as the data inside the columns is consistent in this regard.

  • smpa01's avatar
    smpa01
    Community Champion
    3 years ago

    Fromit87 

     

    let
  func = Splitter.SplitTextByCharacterTransition(
    (c) => not List.Contains({"0" .. "9"}, c), 
    {"0" .. "9"}
  ), 
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WMlTSUTICYmMgNgFiU6XYWAA=", BinaryEncoding.Base64), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [col1 = _t, L1 = _t, L2 = _t, value = _t, category = _t]
  ), 
  Custom1 = Table.TransformColumnNames(
    Source, 
    each 
      let
        x = _, 
        a = try Text.From(func(x){1}) otherwise "", 
        y = if Text.StartsWith(x, "L") then "level" & a else x
      in
        y
  )
in
  Custom1

     

    • Fromit87's avatar
      Fromit87
      Advocate I
      3 years ago

      smpa01 Thank you for your reply & code. However your source table assumes there are already column names starting with L. But this is not the case as stated in the screenshot. The first row of values has values that start with L. So I need to identify for each collumn of the table, if the first row record starts with L and then rename that column to Level 1, the next to Level 2 etc.

       

      source table code looks like this:

      Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIxAhHGQMIEiE2VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t, Merge1 = _t, Merge2 = _t, value = _t, category = _t])

       

  • ams1's avatar
    ams1
    Responsive Resident
    3 years ago

    Hi,

     

    GIVEN

    WHEN

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], // YOUR table here
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Merged.1"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "LevelColumnName", each if Text.StartsWith([Value], "L") then [Index] else null),
    OriginalLevelIndices = List.RemoveNulls(Table.Column(#"Added Custom", "LevelColumnName")),
    NewLevelIndices = List.Numbers(1, List.Count(OriginalLevelIndices)),
    TranslationTable = Table.FromColumns({NewLevelIndices,OriginalLevelIndices}),
    Final = List.Accumulate(Table.ToRows(TranslationTable), #"Added Custom", (t,r) => Table.ReplaceValue(t, r{1}, r{0}, Replacer.ReplaceValue, Table.ColumnNames(t))),
    #"Added Custom1" = Table.AddColumn(Final, "NewColumnNames", each if [LevelColumnName] <> null then "Level" & Text.From([LevelColumnName]) else [Attribute]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute", "Index", "LevelColumnName"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[NewColumnNames]), "NewColumnNames", "Value")
in
    #"Pivoted Column"

    THEN the "level" columns should be renamed

     

    Hope this helps.

    • Fromit87's avatar
      Fromit87
      Advocate I
      3 years ago

      ams1 you're a genius! Thank you so much - the code is easy to understand - awesome.