Create List with M Code from cell(s) in Excel

Question

Hi. I'm building an Excel report template using PowerQuery. The raw data resides in one of many CSV files on a shared server. I've created a "connection only" query that picks up a date value from within the Excel file. I then have the full data query created that finds the correct raw CSV file based on the date and does some basic formatting (column renames, data type changes, etc.).

What I want to do now is create another "connection only" query that picks up values from the Excel file, creates a list out of them, and then uses those values in the full query to filter rows out.

Here's a simplified view of what I have:

• "pr_sourcefile" is the connection only query that picks up the filepath/filename of the required CSV
• The "Create Code List" step currently hard codes a new column [DesiredCodes] containing a list of codes I ultimately wish to retain (including empty quotes)
• The "Create CodeMatch" step returns a TRUE/FALSE value in the new [CodeMatch] column indicating whether the XO_BRAND column matches any of the codes in [Desired Codes]
• "Filter Rows" then retains only those rows where [CodeMatch] is TRUE.

Ultimately I want to replace the hard coded values of "ABC", etc. with the parameter pr_Codes that picks up values from Excel. I've tried using a cell in Excel as my source for the connection only query containing each of the following:

• "ABC","XYZ",""
• ABC,XYZ,
• {"ABC","XYZ",""}

but I can't seem to create a list out of it that returns TRUE for the rows I wish to keep. If I have to change the single reference cell to a dynamic range that'd be ok too. Any help in construction this list parameter is greatly appreciated.

[BTW, the reason I'm creating a list column, then a TRUE/FALSE column in two steps rather than one is that the full version of this will have multiple such filters and I want to keep the steps clean for debugging and future modifications.]

let
    Source = Csv.Document(File.Contents(pr_SourceFile),[Delimiter=",", Columns=12, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promote Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),    
    #"Create Code List" = Table.AddColumn(#"Promote Headers", "DesiredCodes", each {"ABC","XYZ",""}),
    #"Create CodeMatch" = Table.AddColumn(#"Create Code List", "CodeMatch", each List.Contains([DesiredCodes],[XO_BRAND])),
    #"Filter Rows" = Table.SelectRows(#"Create CodeMatch", each [CodeMatch]),
    #"Remove Columns" = Table.RemoveColumns(#"Change Types",{"DesiredCodes", "CodeMatch"})
in
    #"Remove Columns"

Answer 1

Hi @JT Edelstein

• In your Excel file create a table with your codes, something like:
  
• Load it to Power Query as a connection only
• Assuming the above names (TableCodes with column [Code]), in your main query: let
  ...
  DesiredCodes = TableCodes[Code], // or = Table.Column(TableCodes,"Code")
  #"Create CodeMatch" = Table.AddColumn(#"Promote Headers", "CodeMatch", each
  List.Contains(DesiredCodes, [XO_BRAND]), type logical
  ),
  #"Filter Rows" = Table.SelectRows(#"Create CodeMatch", each [CodeMatch]),
  in
  #"Filter Rows"

Sample available here, where TableFromCSV simulates your main query

Answer 2

Glad I could help @@JT Edelstein

You can do it with a single cell in Excel:

Create a blank query, name it DesiredCodes and use this code:

let  
  Source = Table.FirstValue(Excel.CurrentWorkbook(){[Name="pr_Codes"]}[Content]),  
  SourceAsList = Text.Split(Source, ","),  
  ReplacedNull = List.Transform(SourceAsList, each if _ = "null" then null else _)  
in  
  ReplacedNull  

Cheers