M Query to match columns with table

[Anonymous]

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?

[Anonymous]

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.

[Anonymous]

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]

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

[Anonymous]

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]

[ronrsnfld]

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.