PowerQuery Filter on Parameter Value

Question

I have an Excel PowerQuery called FinancialQuery The FinancialQuery is dependent on a Value in a Table called Parameters Every time the user change the parameter value and click Refresh, the query will bring data over based on the user input

However, the last step in the Financial Query is to filter on rows based on the same parameter value instead of having "17213"

#"Filtered Rows On OU" = Table.SelectRows(#"Renamed Columns", each [OperatingUnit] = "17213")

I would like to base the filter on the same function value fullfilepath

#"Filtered Rows On OU" = Table.SelectRows(#"Renamed Columns", each [OperatingUnit] = fullfilepath)

when I put that syntax powerquery is thinking that I'm filtering on the word fullfilepath

Can someone please assist me with the syntax?

Here is my full code

'Parameters Table
Parameter   Value
File Path   17213.csv



' fnGetParameter
(ParameterName as text) =>
 let
     ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
     ParamRow = Table.SelectRows(ParamSource, each ([Parameter]=ParameterName)),
     Value=
    if Table.IsEmpty(ParamRow)=true
    then null
    else Record.Field(ParamRow{0},"Value")
in
    Value


'Financial Query
let
    fullfilepath = fnGetParameter("File Path"),
    Source = SharePoint.Files("https://Personal.sharepoint.com/FOA/BD", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://Personal.sharepoint.com/FOA/BD/Budget/DataSources/Financials/") and ([Extension] = ".csv") and ([Name] = fullfilepath)),
    #"ActiveFile" = #"Filtered Rows"{[Name=fullfilepath,#"Folder Path"="https://Personal.sharepoint.com/FOA/BD/Budget/DataSources/Financials/"]}[Content],
    #"Imported CSV" = Csv.Document(#"ActiveFile",[Delimiter=",", Columns=23, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"P01", Currency.Type}, {"P02", Currency.Type}, {"P03", Currency.Type}, {"P04", Currency.Type}, {"P05", Currency.Type}, {"P06", Currency.Type}, {"P07", Currency.Type}, {"P08", Currency.Type}, {"P09", Currency.Type}, {"P10", Currency.Type},{"P11", Currency.Type},{"P12", Currency.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"P01", "01"}, {"P02", "02"}, {"P03", "03"}, {"P04", "04"}, {"P05", "05"}, {"P06", "06"}, {"P07", "07"}, {"P08", "08"}, {"P09", "09"}, {"P10", "10"}, {"P11", "11"}, {"P12", "12"}}),
    #"Filtered Rows On OU" = Table.SelectRows(#"Renamed Columns", each [OperatingUnit] = "17213")    
in
    #"Filtered Rows On OU"

I have also enclosed a picture of my powerquery syntax.

Codernator Codernator · Accepted Answer · 2017-08-16T14:28:28

I added Text.Replace(fullfilepath,".csv","") Final Syntax

let
    fullfilepath = fnGetParameter("File Path"),
    Source = SharePoint.Files("https://ihcorp.sharepoint.com/FOA/BD", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://ihcorp.sharepoint.com/FOA/BD/Budget/DataSources/Financials/") and ([Extension] = ".csv") and ([Name] = fullfilepath)),
    #"ActiveFile" = #"Filtered Rows"{[Name=fullfilepath,#"Folder Path"="https://ihcorp.sharepoint.com/FOA/BD/Budget/DataSources/Financials/"]}[Content],
    #"Imported CSV" = Csv.Document(#"ActiveFile",[Delimiter=",", Columns=23, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"P01", Currency.Type}, {"P02", Currency.Type}, {"P03", Currency.Type}, {"P04", Currency.Type}, {"P05", Currency.Type}, {"P06", Currency.Type}, {"P07", Currency.Type}, {"P08", Currency.Type}, {"P09", Currency.Type}, {"P10", Currency.Type},{"P11", Currency.Type},{"P12", Currency.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"P01", "01"}, {"P02", "02"}, {"P03", "03"}, {"P04", "04"}, {"P05", "05"}, {"P06", "06"}, {"P07", "07"}, {"P08", "08"}, {"P09", "09"}, {"P10", "10"}, {"P11", "11"}, {"P12", "12"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each [OperatingUnit] = Text.Replace(fullfilepath,".csv",""))
in
    #"Filtered Rows1"

PowerQuery Filter on Parameter Value

1 Answers