Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

M Query to match columns with table

I have a table that contains rows with the following data:

- Column "CustomDataTables" that contains a table
- Column "lookupname" that contains a String value (name of the table)
- Column "ValueThatMayNeedToBeReplaced" that contains a numeric value
- Column "lookupvaluefield" that contains that contains a String value (which is the name of a column that I need to search on the "CustomDataTables" on the same row for the numeric value)
- Column "lookuptextfield" that contains a String value (this is the name of the column on the "CustomDataTables" on the same row that contains the value that I need to return in case there is a match)

I am trying to construct a record from columns "lookupvaluefield" and "ValueThatMayNeedToBeReplaced" to leverage on Table functions and compare.
e.g. "lookupvaluefield" contains CompanyId and "ValueThatMayNeedToBeReplaced" contains 6900 and I need to create a record [CompanyId = 6900].

What is the right syntax?

Need Help
Tutorial Requests

5 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    4 years ago

    An example of your data and expected results from that data, or a workbook showing the same (uploaded to some sharing site and the link posted here), would help you in obtaining some usable responses to your question.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    sample data

     

    I managed to create a record field using the bold statement

     

    // Selecting only custom fields that map to tables
    #"Removed CustomFields that do not map to CustomTables" = Table.SelectRows(#"Expanded CustomFieldDefinitions_STEELMET", each ([lookupname] <> null)),
    #"Changed Type of ValueThatMayNeedToBeReplaced" = Table.TransformColumnTypes(#"Removed CustomFields that do not map to CustomTables",{{"ValueThatMayNeedToBeReplaced", Int64.Type}}),
    // Building records
    #"Build RecordField from lookupvaluefield and ValueThatMayNeedToBeReplaced" = Table.AddColumn(#"Changed Type of ValueThatMayNeedToBeReplaced", "Record to Match on Table", each Record.FromList({[ValueThatMayNeedToBeReplaced]},{[lookupvaluefield]})),
    #"Matching Record on Table" = Table.AddColumn(#"Build RecordField from lookupvaluefield and ValueThatMayNeedToBeReplaced", "Matching Record on Table", each if (Table.Contains([CustomDataTables],[Record to Match on Table] )) then "TRUE" else "FALSE"),
    #"Filtering only matching records" = Table.SelectRows(#"Matching Record on Table", each ([Matching Record on Table] = "TRUE"))

     

    • ronrsnfld's avatar
      ronrsnfld
      Super User
      4 years ago

      And is that what you want?  A single record in each row with the value of [ValueThatMayNeedToBeReplaced] and a Field name of [lookupvaluefield]?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        yes - as a first step I need a single record - then I am using this record to find (TRUE/FALSE) if it is contained on the table contained on [CustomDataTables]. Then I am filtering the records based on "TRUE".

        Ultimately, I am trying to get the lookupvaluetextfield from the table contained on [CustomDataTables]

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    This post might helpful to you.

     

    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.