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'm creating a fact table based on another fact table and i need to do a simple thing but i'm not getting it.
I have something like 5 values columns, each columns bring up aspects of the value. But something is related to all these columns, they all show values related to the total value of the installment program. I have another column that shows that number of installments, all i need to do is divide each of these 5 columns by the column that contains the number of installments.
I tried to use this:
= Table.TransformColumns(NameOfPreviousStep, List.Transform({"Column1", "Column2"}, each {_, (inner) => inner / 45, type number}))
I changed this {"Column1", "Column2"} to the 5 columns that i need to divide
The problem occurs when a change the 45 to the column which contais the number of installment.
I think i have to do something like the "_" to represents each row for the Númber of Installment.
I can definily workaround this, but it seems so simple and usual that I would like to learn what i'm missing here.
I appreciate any help if possible.
Solved! Go to Solution.
Hi - this is how it can be done...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNjFVitWholAsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Installments = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Installments", Int64.Type}}),
ColumnsToDivide = {"Column1", "Column2", "Column3"},
Test = Table.FromRecords(
Table.TransformRows(
#"Changed Type",
(r) =>
Record.TransformFields(
r,
List.Transform(
ColumnsToDivide,
each {_, each _ / r[Installments]}
)
)
),
Value.Type(#"Changed Type")
)
in
Test
Even simpler: unpivot the columns so the values you want to divide are all in the same column, then divide this column and re-pivot them.
=Table.ReplaceValue(NameOfPreviousStep,each [NoOfInstallment],"",(x,y,z)=>x/y,{"Column1","Column2","Column3"})
Hi - this is how it can be done...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNjFVitWholAsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Installments = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Installments", Int64.Type}}),
ColumnsToDivide = {"Column1", "Column2", "Column3"},
Test = Table.FromRecords(
Table.TransformRows(
#"Changed Type",
(r) =>
Record.TransformFields(
r,
List.Transform(
ColumnsToDivide,
each {_, each _ / r[Installments]}
)
)
),
Value.Type(#"Changed Type")
)
in
Test
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.