Running Power Queries using dynamic parameter table from Excel with multiple values for same field

[peppy]

Hi,

I have been struggling with this issue for a while. I am trying to use a table (1 column called "Project", x-number of rows) called "Parameter" in a worksheet to enter project numbers that I want to run a query against. This table could have one project number, two project numbers, or several project numbers based on what the user wants. The query will then take these project numbers and filter the transaction table based on the projects in the "Parameter" table. I have found a way to do this, but I have to manually write M-code for each value (row) in the Parameter table and reference this in the query code. My question is this: Is there a way to pass all the values in a "Parameter"-table (not knowing how many projects the user wants to enter) to the query without defining each one? I am hoping not to have to limit the table to 5 projects only or 10 projects only etc. See my example that works for two project numbers in the Parameter table below:

let
   
Project_Parameter = Excel.CurrentWorkbook(){[Name="Parameter"]}[Content],
[COLOR="#0000FF"]Project_Value = Project_Parameter{0}[Project],
Project_Value1 = Project_Parameter{1}[Project],[/COLOR]


 Source = Oracle.Database("ifs8prod"),
    IFSAPP_GEN_LED_PROJ_VOUCHER_ROW = Source{[Schema="IFSAPP",Item="GEN_LED_PROJ_VOUCHER_ROW"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(IFSAPP_GEN_LED_PROJ_VOUCHER_ROW,{"ACCOUNTING_YEAR", "VOUCHER_TYPE", "VOUCHER_NO", "AMOUNT", "PROJECT_ACTIVITY_ID", "TEXT", "REFERENCE_SERIE", "REFERENCE_NUMBER", "ACCOUNT", "ACCOUNT_DESC", "PROJECT_ID", "ACCOUNTING_PERIOD", "YEAR_PERIOD_KEY"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns" , [COLOR="#0000FF"]each ([PROJECT_ID] = Number.ToText(Project_Value, "D", "") or ([PROJECT_ID] = Number.ToText(Project_Value1, "D", [/COLOR]""))))

   in
#"Filtered Rows"

Regards,

Peppy

 

[billszysz]

let

Project_Parameter = Excel.CurrentWorkbook(){[Name="Parameter"]}[Content][Project],
//Project_Value = Project_Parameter{0}[Project],
//Project_Value1 = Project_Parameter{1}[Project],


Source = Oracle.Database("ifs8prod"),
IFSAPP_GEN_LED_PROJ_VOUCHER_ROW = Source{[Schema="IFSAPP",Item="GEN_LED_PROJ_VOUCHER_ROW"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(IFSAPP_GEN_LED_PROJ_VOUCHER_ROW,{"ACCOUNTING_YEAR", "VOUCHER_TYPE", "VOUCHER_NO", "AMOUNT", "PROJECT_ACTIVITY_ID", "TEXT", "REFERENCE_SERIE", "REFERENCE_NUMBER", "ACCOUNT", "ACCOUNT_DESC", "PROJECT_ID", "ACCOUNTING_PERIOD", "YEAR_PERIOD_KEY"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns" , each List.Contains(Project_Parameter,[PROJECT_ID]))

in
#"Filtered Rows"

 

[ImkeF]

Another way to filter one table with another table would be to merge them on "matching rows only" (JoinKind.Inner), as described in this post here:

Want to filter a PivotTable based on an external range or list? – The BIccountant

 

[peppy]

Hi, and thanks to both of you for your quick response. I think this is exactly what I am looking for, but I think I just need to tweek it a bit. I tried the code you provided billszysz, but I get in trouble with the number type, since I have to convert the number to text in order for this to work. I used the Number.ToText for each individual value, but this seems not to work for the List.Contains(). How can I convert the values in the List.Contains() to text? Any ideas? Here is the error message I got: Unexpected error: ORA-01722: invalid number

 

[ImkeF]

Hm, not familiar with the error message. Try Text.From instead.

 

[peppy]

Just an update to my post. The problem got resolved by using ChangedType = List.Transform(Project_Parameter,Number.ToText), to transform the values to text. Thanks again for all your help.