Change column names based on a condition

[Fromit87]

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!

[ams1 · Accepted solution]

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]

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

[smpa01]

@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]

@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])

[Poohkrd]

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

[Fromit87]

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