Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi All
I am transforming a bunch of columns by multiplying them on row level by values in a column:
Ex in tabel below I multiply Column2, Column3 & Column4 by Column1 (all columns 1-4 are data type: decimal numbers)
Column1 | Column2 | Column3 | Column4 |
2 | 1 | 2 | 1 |
3 | 1 | 1 | 3 |
Using:
Table.FromRecords(Table.TransformRows(#"Changed Type", (x) => Record.TransformFields(x, List.Transform({"Column2", "Column3", "Column4"}, (y) => {y, each x[Column1] * _}))))
And get:
Column1 | Column2 | Column3 | Column4 |
2 | 2 | 4 | 2 |
3 | 3 | 3 | 9 |
Wierd thing is that pre transformation Column 2, 3 & 4 are Decimal Numbers and post transformation they are datat type: any so I transforma all the columns to decimal number in next step.
And I´m finding some smal errors that feels like rounding errors on some rows.
Anyone out there with an Idea what I´m doing wrong?
Solved! Go to Solution.
Hi @MansRydell ,
You could use the Table.TransformColumnTypes function to change all the data types to decimal number.
= let
output=Table.FromRecords(Table.TransformRows(#"Changed Type", (x) => Record.TransformFields(x, List.Transform({"Column2", "Column3", "Column4"}, (y) => {y, each x[Column1] * _}))))
in
Table.TransformColumnTypes(output,List.Transform(Table.ColumnNames(output), each {_, type number}))
Table.TransformColumnTypes - PowerQuery M | Microsoft Learn
List.Transform - PowerQuery M | Microsoft Learn
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Try this solution with Table.ReplaceValue
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HYyxDQAgDMN+6ZyhNEDKLYj/36DqZCm2cq8JyTQYsTmKC6FZ3NhOe7hlOFYbnsKEnygOLKqDgKsGQVI/eXYXSnvvAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
Number_Type = Table.TransformColumnTypes( Source, {{"Column1", type number}, {"Column2", type number}, {"Column3", type number}, {"Column4", type number}}),
Replace_Value = Table.ReplaceValue( Number_Type, each [Column1], "", (x,y,z) => x*y, {"Column2", "Column3", "Column4"}),
Restore_Type = Value.ReplaceType( Replace_Value, Value.Type( Number_Type ))
in
Restore_Type
Stéphane
Thanks everyone for you´re input. Unfortunately my issue is unresolved thanks for you´re effort if I find a solution I´ll post it. 🙏
Hi @MansRydell ,
You could use the Table.TransformColumnTypes function to change all the data types to decimal number.
= let
output=Table.FromRecords(Table.TransformRows(#"Changed Type", (x) => Record.TransformFields(x, List.Transform({"Column2", "Column3", "Column4"}, (y) => {y, each x[Column1] * _}))))
in
Table.TransformColumnTypes(output,List.Transform(Table.ColumnNames(output), each {_, type number}))
Table.TransformColumnTypes - PowerQuery M | Microsoft Learn
List.Transform - PowerQuery M | Microsoft Learn
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @MansRydell,
Alternatively, you could give something like this a go.
Copy this code into a new blank query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUTIEYggdqxOtZAwVAWFjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
ChType = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}}),
ReplaceValue = List.Accumulate(
List.Skip( Table.ColumnNames(Source), 1),
ChType,
(s, a) => Table.ReplaceValue( s,
each Record.Field(_, a),
each Record.Field(_, a) * Record.Field(_, "Column1"),
Replacer.ReplaceValue, {a}
)
),
RestoreType = Value.ReplaceType( ReplaceValue, Value.Type( ChType ))
in
RestoreType
There are a number of transformations that can't retain type, above type is restored as it was before.
Here's a link to a good post that deals with rounding in M
Ps. Please mark this answer as solution when it helped you to resolve your question, thanks!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
12 | |
11 | |
11 | |
8 |
User | Count |
---|---|
43 | |
25 | |
16 | |
15 | |
12 |