Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Fromit87
Advocate I
Advocate I

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!

 

Fromit87_0-1675865183445.png

 

1 ACCEPTED SOLUTION
ams1
Super User
Super User

Hi,

 

GIVEN

ams1_0-1675930917028.png

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

ams1_1-1675931026640.png

 

Hope this helps.

View solution in original post

6 REPLIES 6
ams1
Super User
Super User

Hi,

 

GIVEN

ams1_0-1675930917028.png

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

ams1_1-1675931026640.png

 

Hope this helps.

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

smpa01
Super User
Super User

@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

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@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
Advocate I
Advocate I

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors