Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
@ams1 you're a genius! Thank you so much - the code is easy to understand - awesome.
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
@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])
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.