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:
Regards,
Peppy
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:
Code:
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