cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SBuri Member
Member

Table Transformations for Dynamic Columns

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 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Table Transformations for Dynamic Columns

HI @SBuri

 

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

 

 

Highlighted
Super User
Super User

Re: Table Transformations for Dynamic Columns

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

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




4 REPLIES 4
Super User
Super User

Re: Table Transformations for Dynamic Columns

I do not but @ImkeF might.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User
Super User

Re: Table Transformations for Dynamic Columns

HI @SBuri

 

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

 

 

Highlighted
Super User
Super User

Re: Table Transformations for Dynamic Columns

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

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




SBuri Member
Member

Re: Table Transformations for Dynamic Columns

@ImkeF@Zubair_Muhammad@Greg_Deckler - Thank you so much. You all are awesome and I really appreciate the help. Brilliant solutions!

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 6 members 3,199 guests
Please welcome our newest community members: