Forum Discussion
M Query to match columns with table
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"))
And is that what you want? A single record in each row with the value of [ValueThatMayNeedToBeReplaced] and a Field name of [lookupvaluefield]?
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]