Forum Discussion
Solved
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 fieldn...
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
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
ToRecord
Thank 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!
