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,
Input:
I have a table with changing #'s of columns.
Process:
I need to take the first two characters of these columns. So I need PQ to dynamically identify which columns to take the first two characters from depending on their header name. For instance, something like for all columns that start with ABCD, take the first two characters of every row.
Problem:
I know how to make a list of the headers that start with ABCD. But I don't know how to pass that list to a TableTransformColumns_Text.Start(ColumnList, 2). Where columnlist is Power BI only seems to accept 1 column value at a time, and if more are desired the code needs to be repeated with the new column name which of course won't work dynamically.
Any ideas on how to pass dynamic column amounts through a table transformation text.start? Thanks!
For ex: The below code works with removing columns dynamically. Something like this but with Text.Start
Source = Table.FromRows({{"New York", 23, 51, 732}, {"Chicago", 25, 421, 23}, {"Los Angeles", 632, 22, 423}}, {"City", "Value 1", "Value 2", "Column 3"}), ColumnsToRemove = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "V")), RemovedColumns = Table.RemoveColumns(Source, ColumnsToRemove)
Thanks,
Buri
Solved! Go to Solution.
HI @Anonymous
If I understand the problem correctly
Suppose in your above sample, you want to transform the Columns starting with V
for example multiply all values by 10 or divide them by 10 you can use something like this
let Source = Table.FromRows({{"New York", 23, 51, 732}, {"Chicago", 25, 421, 23}, {"Los Angeles", 632, 22, 423}}, {"City", "Value 1", "Value 2", "Column 3"}), ColumnsToTransform= List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "V")), ActionToPerform=List.Repeat({each _ *10},List.Count(ColumnsToTransform)), Transformed=Table.TransformColumns(Source,List.Zip({ColumnsToTransform,ActionToPerform})) in Transformed
Please try this function:
(InputTable as table, ListOfColNames, TransformFunction as function) => let TransformFunctionList = List.Transform(ListOfColNames, (x) => {x, TransformFunction}), Transform = Table.TransformColumns(InputTable, TransformFunctionList) in Transform
Name it "fnTransformMany" and call it like so:
let Source = Table.FromRows({{"New York", "23", "51", "732"}, {"Chicago", "25", "421", "23"}, {"Los Angeles", "632", "22", "423"}}, {"City", "Value 1", "Value 2", "Column 3"}), ColumnsToRemove = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "V")), Result = fnTransformMany(Source, ColumnsToRemove, each Text.Start(_, 2)) in Result
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I do not but @ImkeF might.
Please try this function:
(InputTable as table, ListOfColNames, TransformFunction as function) => let TransformFunctionList = List.Transform(ListOfColNames, (x) => {x, TransformFunction}), Transform = Table.TransformColumns(InputTable, TransformFunctionList) in Transform
Name it "fnTransformMany" and call it like so:
let Source = Table.FromRows({{"New York", "23", "51", "732"}, {"Chicago", "25", "421", "23"}, {"Los Angeles", "632", "22", "423"}}, {"City", "Value 1", "Value 2", "Column 3"}), ColumnsToRemove = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "V")), Result = fnTransformMany(Source, ColumnsToRemove, each Text.Start(_, 2)) in Result
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@ImkeF, @Zubair_Muhammad, @Greg_Deckler - Thank you so much. You all are awesome and I really appreciate the help. Brilliant solutions!
HI @Anonymous
If I understand the problem correctly
Suppose in your above sample, you want to transform the Columns starting with V
for example multiply all values by 10 or divide them by 10 you can use something like this
let Source = Table.FromRows({{"New York", 23, 51, 732}, {"Chicago", 25, 421, 23}, {"Los Angeles", 632, 22, 423}}, {"City", "Value 1", "Value 2", "Column 3"}), ColumnsToTransform= List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "V")), ActionToPerform=List.Repeat({each _ *10},List.Count(ColumnsToTransform)), Transformed=Table.TransformColumns(Source,List.Zip({ColumnsToTransform,ActionToPerform})) in Transformed
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.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |