Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. 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 @Anonymous ,
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 @Anonymous ,
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 @Anonymous,
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!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |