Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a 'data' table where I'd like to dynamically change the number of decimal places.
I use a 'dictionary' table that holds the names of the columns in 'data' along with the desired number of decimal places, e.g.
ColumnName | Decimal |
Input | 2 |
Temperature | 1 |
Pressure | 0 |
Weight | 3 |
I would like to be able to perform a TransformColumns operation on 'data', such as
Table.TransformColumns(data, {{"Temperature", each Number.Round(_, 1), type number}})
MinDecimal = List.Min(dictionary[Decimal]),
MaxDecimal = List.Max(dictionary[Decimal]),
Range = List.Generate(() => MinDecimal, each _ <= MaxDecimal, each _ + 1)
It feels like I could then use a recursive function (with or without the Range list) to select one by one the columns that need each transform, something like the answer from @ImkeF in Table Transformations for Dynamic Columns :
fnTransform = (InputTable as table, ListOfColNames, TransformFunction as function) => let TransformFunctionList = List.Transform(ListOfColNames, (x) => {x, TransformFunction}), Transform = Table.TransformColumns(InputTable, TransformFunctionList) in Transform
// Somehow loop n = from MinDecimal to MaxDecimal
ColumnsToTransform = Table.SelectRows(dictionary, each [Decimal] = n)[ColumnName],
Transform = fnTransform(data, ColumnsToTransform, each Number.Round(_, n), type number)
but I am stumped as to the way to use a loop to make this work...
Solved! Go to Solution.
Hi @ChemEnger ,
it could work like so:
(InputTable as table, dictionary as table) =>
let
TransformFunctionList = List.Transform(
Table.ToRecords(dictionary),
(record) => {record[ColumnName], each Number.Round(_, record[Decimal]), type number}
),
Transform = Table.TransformColumns(InputTable, TransformFunctionList)
in
Transform
No real recursion needed. Just transform your dictionary table into a list of records and create the transformation list elements out of it.
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 @ChemEnger ,
it could work like so:
(InputTable as table, dictionary as table) =>
let
TransformFunctionList = List.Transform(
Table.ToRecords(dictionary),
(record) => {record[ColumnName], each Number.Round(_, record[Decimal]), type number}
),
Transform = Table.TransformColumns(InputTable, TransformFunctionList)
in
Transform
No real recursion needed. Just transform your dictionary table into a list of records and create the transformation list elements out of it.
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
Spot on, thank you @ImkeF.
I was looking at something similar on Medium - Transform Multiple Columns Based On Other Columns In Power Query but your solution worked first time 🙂
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.