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, L...
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.

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