Forum Discussion
Multiply two records
Hey,
I have to records with the same fieldnames, but different values, for instance:
Record1:
fieldname1: 10
fieldname2: 20
fieldname3: 30
Record2:
fieldname1: 40
fieldname2: 50
fieldname3: 60
What I want is a third Record with the fields multiplied by each other,
so:
Record3:
fieldname1: Record1.fieldname1 * Record2.fieldname1 (=400)
fieldname2: Record1.fieldname2 * Record2.fieldname2 (=1000)
fieldname3: Record1.fieldname3 * Record2.fieldname3 (=1800)
The fieldnames are not known literally before, so they shouldn't appear in the code. The code should work with any number of fieldnames that are common in both records and generate the third record as described.
And the fields in the records are not necessarily in the same order, furthermore, there can be fields in Record 1 or 2, which are not present in the other. These field can be skipped, they don't need to appear in Record 3.
I'd like to solve it in Power Query, not in DAX.
How can I achieve that? Is a record the right dataype to do it, or ist a list or a table better? I consider a list a bad idea, since the fields can be in a different order.
Thank You very much for any hint.
Quick and dirty solution (there's probably a more elegant solution, but I don't have time for it now):
let #"1stRecord" = [A=1, B=2, C=3], #"2ndRecord" = [C=30, A=10], CommonFields = List.Intersect({Record.FieldNames(#"1stRecord"),Record.FieldNames(#"2ndRecord")}), #"1stFields" = Record.SelectFields(#"1stRecord", CommonFields), #"2ndFields" = Record.SelectFields(#"2ndRecord", CommonFields), ListOfRecordFields = List.Zip({Record.FieldValues(#"1stFields"), Record.FieldValues(#"2ndFields")}), ListMultiplication = List.Transform(ListOfRecordFields, each _{0} * _{1}), ToTable = Table.FromRows({ListMultiplication}, CommonFields), ToRecord = Table.ToRecords(ToTable){0} in ToRecord
3 Replies
It's a good thing that you don't want DAX because you can't generate rows in an existing table using DAX. So, hopefully ImkeF has a solution for you in M code.
Quick and dirty solution (there's probably a more elegant solution, but I don't have time for it now):
let #"1stRecord" = [A=1, B=2, C=3], #"2ndRecord" = [C=30, A=10], CommonFields = List.Intersect({Record.FieldNames(#"1stRecord"),Record.FieldNames(#"2ndRecord")}), #"1stFields" = Record.SelectFields(#"1stRecord", CommonFields), #"2ndFields" = Record.SelectFields(#"2ndRecord", CommonFields), ListOfRecordFields = List.Zip({Record.FieldValues(#"1stFields"), Record.FieldValues(#"2ndFields")}), ListMultiplication = List.Transform(ListOfRecordFields, each _{0} * _{1}), ToTable = Table.FromRows({ListMultiplication}, CommonFields), ToRecord = Table.ToRecords(ToTable){0} in ToRecordThank You so much, it works fine.
Most parts of the algorithm are similar to what I tried, but the point I couldn't figure out was the syntax:
each _{0} * _{1}in
List.Transform(ListOfRecordFields, each _{0} * _{1})So, Thanks a lot!
