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.
I have wrote this function that combines Col1, Col2, Col3 from SourceTbl into single Col with a common IDCol:
(SourceTbl as table, IDCol as text, Col as text, Col1 as text, Col2 as text, Col3 as text) => let KeepOnlyCol1 = Table.SelectColumns(SourceTbl,{Col1, IDCol}), RenameCol1 = Table.RenameColumns(KeepOnlyCol1,{{Col1, Col}}), KeepOnlyCol2 = Table.SelectColumns(SourceTbl,{Col2, IDCol}), RenameCol2 = Table.RenameColumns(KeepOnlyCol2,{{Col2, Col}}), KeepOnlyCol3 = Table.SelectColumns(SourceTbl,{Col3, IDCol}), RenameCol3 = Table.RenameColumns(KeepOnlyCol3,{{Col3, Col}}), AppendCols = Table.Combine({RenameCol1, RenameCol2, RenameCol3}) in AppendCols
How do modify this function so that:
1. It allows for col4, col5, ... coln (as many as the user inputs)
2. With each col, it reproduces the 2 steps:
KeepOnlyColn = Table.SelectColumns(SourceTbl,{Coln, IDCol}), RenameColn = Table.RenameColumns(KeepOnlyColn,{{Coln, Col}}),
Then add the end append all output tables together:
AppendCols = Table.Combine({RenameCol1, RenameCol2, RenameCol3, ... RenameColn})
Solved! Go to Solution.
Hi @freelensia ,
I believe sth like this should work:
(SourceTbl as table, IDCol as text, Col as text, ColNames)) => let Transform = List.Transform(ColNames, each Table.RenameColumns( Table.SelectColumns(SourceTbl, {_, IDCol}), {_, Col}) ) AppendCols = Table.Combine(Transform) in AppendCols
Where ColNames has to be filled in as list. So the users just have to pass a list with column names into the last function parameter.
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
Hi @freelensia ,
I believe sth like this should work:
(SourceTbl as table, IDCol as text, Col as text, ColNames)) => let Transform = List.Transform(ColNames, each Table.RenameColumns( Table.SelectColumns(SourceTbl, {_, IDCol}), {_, Col}) ) AppendCols = Table.Combine(Transform) in AppendCols
Where ColNames has to be filled in as list. So the users just have to pass a list with column names into the last function parameter.
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
Thanks @ImkeF for this solution. Could you help explain the logic of it?
I can see that the query will go through each item in ColNames List and transform it in some way. But I don't understand the logic of the Rename. What is the meaning of _ here?
Just recognized that my answer on this question was lost...
So here it comes again:
The underscore stands for each element in the List of ColumnNames.
As this is iterated through, you need an expression in the syntax that represents each element during the iteration.
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
Hi @freelensia ,
I'm not understand your requirement completely.
I'm not good at Power Query. Hope @ImkeF could have ideas for your scenario.
Best Regard,
Cherry
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.