Forum Discussion
How do I Filter a Power Query field If an Excel named range has data in it
I am using the function GetNamedRange
(Reference: How To Reference A Named Range In Power Query | How To Excel)
I want to filter the field YYYYMM in a Power to the value stored in the range named: rng_YYYYMM;
however, if the named range is empty, I do not want the field filtered.
How can I accomplish this? I "assume" I need an if statement of some sort, but am hoping I do not have to use a Conditional Column (I don't think that would accomplish my objective).
Your help will be greatly appreciated.
I found a method that worked for me.
I searched the internet and found:
How To Parameterize Your Power QueryI.E., I inserted the following function in Power Query and named it: fParameter
let Parameter=(TableName,ParameterLabel) => let Source = Excel.CurrentWorkbook(){[Name=TableName]}[Content], value = Source{[Parameter=ParameterLabel]}[Value] in value in ParameterThen on my Excel worksheet I create a table named: tbl_Parameters (cells: A3:B9).
Finally, my Power query that works is:
let Source = Csv.Document(File.Contents("Q:\SO-Finance\PSft_Qry_csv_Files\NDS325_RT_HISTORY_W_SD_4_BIEN.csv"),[Delimiter=",", Columns=42, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Amount", type number}, {"Jrnl_Date", type text}, {"Downloaded", type datetime}, {"From_Jrnl_or_AP_Actg_Date", type date}, {"Thru_Jrnl_or_AP_Actg_Date", type date}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"CB_Subdivision", Order.Ascending}, {"Dept_ID", Order.Ascending}, {"Class", Order.Ascending}, {"Proj", Order.Ascending}, {"Fund", Order.Ascending}, {"Act_ID", Order.Ascending}, {"Jrnl_Date", Order.Ascending}}), #"Filtered Jrnl_Dates" = Table.SelectRows(#"Sorted Rows", each ([Jrnl_Date] >= fParameter("tbl_Parameters","param_Starting_Jrnl_Date") and [Jrnl_Date] <= fParameter("tbl_Parameters","param_Ending_Jrnl_Date"))), #"Filtered Subdivision" = Table.SelectRows(#"Filtered Jrnl_Dates", if fParameter("tbl_Parameters","param_Subdivision_Starts_With") > " " then each (Text.StartsWith([CB_Subdivision], fParameter("tbl_Parameters","param_Subdivision_Starts_With"))) else each ([CB_Subdivision] > " ")), #"Filtered Dept_ID" = Table.SelectRows(#"Filtered Subdivision", if fParameter("tbl_Parameters","param_Dept_ID") > " " then each (Text.StartsWith([Dept_ID], fParameter("tbl_Parameters","param_Dept_ID"))) else each ([Dept_ID] > " ")), #"Filtered Class" = Table.SelectRows(#"Filtered Dept_ID", if fParameter("tbl_Parameters","param_Class") > " " then each (Text.StartsWith([Class], fParameter("tbl_Parameters","param_Class"))) else each ([Class] > " ")), #"Filtered Acct" = Table.SelectRows(#"Filtered Class", if fParameter("tbl_Parameters","param_Acct") > " " then each (Text.StartsWith([Acct], fParameter("tbl_Parameters","param_Acct"))) else each ([Acct] > " ")) in #"Filtered Acct"It works GREAT!
2 Replies
Can you count the rows? If I have this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Test = _t]), EmptyData = if Table.RowCount(Source) = 0 then true else false in EmptyDataThe EmptyData step above simply counts the rows in Source. You would replace true/false with your filter statement of it is false, or whatever you wanted to replace it with if it is true.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.I found a method that worked for me.
I searched the internet and found:
How To Parameterize Your Power QueryI.E., I inserted the following function in Power Query and named it: fParameter
let Parameter=(TableName,ParameterLabel) => let Source = Excel.CurrentWorkbook(){[Name=TableName]}[Content], value = Source{[Parameter=ParameterLabel]}[Value] in value in ParameterThen on my Excel worksheet I create a table named: tbl_Parameters (cells: A3:B9).
Finally, my Power query that works is:
let Source = Csv.Document(File.Contents("Q:\SO-Finance\PSft_Qry_csv_Files\NDS325_RT_HISTORY_W_SD_4_BIEN.csv"),[Delimiter=",", Columns=42, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Amount", type number}, {"Jrnl_Date", type text}, {"Downloaded", type datetime}, {"From_Jrnl_or_AP_Actg_Date", type date}, {"Thru_Jrnl_or_AP_Actg_Date", type date}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"CB_Subdivision", Order.Ascending}, {"Dept_ID", Order.Ascending}, {"Class", Order.Ascending}, {"Proj", Order.Ascending}, {"Fund", Order.Ascending}, {"Act_ID", Order.Ascending}, {"Jrnl_Date", Order.Ascending}}), #"Filtered Jrnl_Dates" = Table.SelectRows(#"Sorted Rows", each ([Jrnl_Date] >= fParameter("tbl_Parameters","param_Starting_Jrnl_Date") and [Jrnl_Date] <= fParameter("tbl_Parameters","param_Ending_Jrnl_Date"))), #"Filtered Subdivision" = Table.SelectRows(#"Filtered Jrnl_Dates", if fParameter("tbl_Parameters","param_Subdivision_Starts_With") > " " then each (Text.StartsWith([CB_Subdivision], fParameter("tbl_Parameters","param_Subdivision_Starts_With"))) else each ([CB_Subdivision] > " ")), #"Filtered Dept_ID" = Table.SelectRows(#"Filtered Subdivision", if fParameter("tbl_Parameters","param_Dept_ID") > " " then each (Text.StartsWith([Dept_ID], fParameter("tbl_Parameters","param_Dept_ID"))) else each ([Dept_ID] > " ")), #"Filtered Class" = Table.SelectRows(#"Filtered Dept_ID", if fParameter("tbl_Parameters","param_Class") > " " then each (Text.StartsWith([Class], fParameter("tbl_Parameters","param_Class"))) else each ([Class] > " ")), #"Filtered Acct" = Table.SelectRows(#"Filtered Class", if fParameter("tbl_Parameters","param_Acct") > " " then each (Text.StartsWith([Acct], fParameter("tbl_Parameters","param_Acct"))) else each ([Acct] > " ")) in #"Filtered Acct"It works GREAT!
