Forum Discussion

WayneC's avatar
WayneC
Regular Visitor
3 years ago
Solved

Use reference table column as criteria in creating custom column in primary table

This may seem simple, I have been looking at several posts here on the forum, but cannot find a solution that I can even modify to fit my situation. I have two tables.. The primary table is used for...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    =Table.FromRecords(Table.TransformRows(#"Renamed Columns",each _&Record.FromTable(#table({"Name","Value"},List.Transform({"Cristian","Melvin","Adam","Mario"},(x)=>{"cc_"&x,if Text.Contains([foreman],x) then "Yes" else "No"})))))

  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    =Table.FromRecords(Table.TransformRows(#"Renamed Columns",each _&Record.FromTable(#table({"Name","Value"},List.Transform(ReferrenceTable[NameList],(x)=>{"cc_"&x,if Text.Contains([foreman],x) then "Yes" else "No"})))))

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
3 years ago

=Table.FromRecords(Table.TransformRows(#"Renamed Columns",each _&Record.FromTable(#table({"Name","Value"},List.Transform({"Cristian","Melvin","Adam","Mario"},(x)=>{"cc_"&x,if Text.Contains([foreman],x) then "Yes" else "No"})))))

  • WayneC's avatar
    WayneC
    Regular Visitor
    3 years ago

    Works as expected.  Is it possible to remove the hard coded names and use the name from the reference table (in the event the names change, would not want to have to search and replace them all).

     

    • wdx223_Daniel's avatar
      wdx223_Daniel
      Community Champion
      3 years ago

      =Table.FromRecords(Table.TransformRows(#"Renamed Columns",each _&Record.FromTable(#table({"Name","Value"},List.Transform(ReferrenceTable[NameList],(x)=>{"cc_"&x,if Text.Contains([foreman],x) then "Yes" else "No"})))))

      • WayneC's avatar
        WayneC
        Regular Visitor
        3 years ago

        Works perfectly , thank you!