Forum Discussion

pkoevesdi's avatar
pkoevesdi
Regular Visitor
7 years ago
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...
Need Help
  • ImkeF's avatar
    ImkeF
    7 years ago

    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

     

ImkeF's avatar
ImkeF
Community Champion
7 years ago

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

 

pkoevesdi's avatar
pkoevesdi
Regular Visitor
7 years ago

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!