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.
Hello,
I have a table with certain columns names, and I would like to remove some containing a defined string.
An example below where I only used a string "budget" as I am not sure how to do it with the whole list (ListTextToBeRemoved) as an example....
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"2023 Plan" = _t, #"2024 Plan" = _t, #"Planned budget" = _t, #"Internal budget" = _t, #"Jan Forecast" = _t, #"Feb Forecast" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"2023 Plan", type text}, {"2024 Plan", type text}, {"Planned budget", type text}, {"Internal budget", type text}, {"Jan Forecast", type text}, {"Feb Forecast", type text}}),
ListTextToBeRemoved={"Plan","budget"},
ColumnstoBeRemoved=List.FindText(Table.ColumnNames( #"Changed Type"), "budget")
in
ColumnstoBeRemoved
Thanks for your help!
Kind regards
Valeria
Solved! Go to Solution.
Hello, @ValeriaBreve I made it case insensitive. If you don't want that - remove comparer.
let
ListTextToBeRemoved={"Plan","budget"},
column_names = {"2023 plan", "2023 budget", "2024 fact", "2023 fact", "222 BUDGET"},
ColumnstoBeRemoved =
List.Select(
column_names,
each
List.ContainsAny(
{_},
ListTextToBeRemoved,
(x, y) => Text.Contains(x, y, Comparer.OrdinalIgnoreCase)))
in
ColumnstoBeRemoved
Hello, @ValeriaBreve I made it case insensitive. If you don't want that - remove comparer.
let
ListTextToBeRemoved={"Plan","budget"},
column_names = {"2023 plan", "2023 budget", "2024 fact", "2023 fact", "222 BUDGET"},
ColumnstoBeRemoved =
List.Select(
column_names,
each
List.ContainsAny(
{_},
ListTextToBeRemoved,
(x, y) => Text.Contains(x, y, Comparer.OrdinalIgnoreCase)))
in
ColumnstoBeRemoved
@AlienSx @wdx223_Daniel Hello, thank you SO MUCH! I am still not mastering functions 😞 I hope it will come one day by learning and doing....
So just from a practical standpoint - I need to accept a solution only - I can't do for both - and you both replied so fast, thank you so much for this. I have decided to give the solution to @AlienSx as there is the extra step of the case insensitivity - and I will give kudos to @wdx223_Daniel - I hope this is OK!!!!!!
Thanks again, Valeria
ColumnstoBeRemoved=List.Select(Table.ColumnNames( #"Changed Type"), each List.Contains(ListTextToBeRemoved,_,(x,y)=>Text.Contains(y,x)))
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.