Using a Parameters Table

[rudi.prozesky]

Hello.

I've gone through the blog posts and been bumping my head with this for a few hour now. I'd greatly appreciate any help. I'm sure I'm missing something something small.

I have an excel table named "tbl_Parameters" with the following contents:

Parameter	Value
Workbook File Path	Z:\Department - ACCOUNTING\SEC Reporting\2017Q2\
FAM Additions File Path	Z:\Department - ACCOUNTING\SEC Reporting\2017Q2\Reports\FAM Additions for Month (Based on when Assets Generated) YTD 06-17
Balance Sheet Detail File Path	Z:\Department - ACCOUNTING\SEC Reporting\2017Q2\Reports\Balance Sheet Detail by Account YTD 06-17
FAM Transfers File Path	Z:\Department - ACCOUNTING\SEC Reporting\2017Q2\Reports\FAM Transfers for FAM Accounts This Fiscal Year to Report Ran Date 06-17
FAM Sales Disposals File Path	Z:\Department - ACCOUNTING\SEC Reporting\2017Q2\Reports\FAM Assets Sales & Disposals This Fiscal Year to Report Ran Date 06-17
FAM Depreciation File Path	Z:\Department - ACCOUNTING\SEC Reporting\2017Q2\Reports\FAM Depreciation tie to GL YTD 06-17

I wrote a fnGetParameter connection:

(ParameterName as text) =>
let
ParamSource = Excel.CurrentWorkbook(){[Name="tbl_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


I started my Query:

let
FilePath = fnGetParameter("FAM Depreciation File Path"),


Source = Csv.Document(File.Contents(FilePath),[Delimiter=",", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"JE Line Internal ID", Int64.Type}, {"FAM Internal ID", Int64.Type}, {"Asset", type text}, {"Asset Type", type text}, {"Subsidiary", type text}, {"Transaction Amount", type number}, {"Depreciation Posting Reference", type text}, {"Date Created", type datetime}, {"Depreciation Date", type date}, {"Depreciation Account", type text}, {"Period", type date}})
in
#"Changed Type"

And I get this error:

DataSource.Error: Could not find file 'Z:\Department - ACCOUNTING\SEC Reporting\2017Q2\Reports\FAM Depreciation tie to GL YTD 06-17'.
Details:
Z:\Department - ACCOUNTING\SEC Reporting\2017Q2\Reports\FAM Depreciation tie to GL YTD 06-17

Thanks for looking at this!
Rudi

 

[retired007geek]

Rudi,

You haven't included the file types in your paths, e.g. .xlsx or .csv, etc. Thus the file can not be found. Unless of course you have extensionless file names.

HTH

 

[rudi.prozesky]

Thanks so much! It's working.

 

[Bob Phillips]

As an aside, you have the path in that table so why not use it and reduce the duplication

Parameter	Value
Workbook File Path	Z:\Department - ACCOUNTING\SEC Reporting\2017Q2\
FAM Additions File	Reports\FAM Additions for Month (Based on when Assets Generated) YTD 06-17.xlsx
Balance Sheet Detail File	Reports\Balance Sheet Detail by Account YTD 06-17.xlsx
FAM Transfers File	Reports\FAM Transfers for FAM Accounts This Fiscal Year to Report Ran Date 06-17.xlsx
FAM Sales Disposals File	Reports\FAM Assets Sales & Disposals This Fiscal Year to Report Ran Date 06-17.xlsx
FAM Depreciation File	Reports\FAM Depreciation tie to GL YTD 06-17.xlsx

and the code would then be

let
    FilePath = fnGetParameter("Workbook File Path"),
    FileName = FilePath & fnGetParameter("FAM Depreciation File"),
    Source = Csv.Document(File.Contents(FileName),[Delimiter=",", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"JE Line Internal ID", Int64.Type}, {"FAM Internal ID", Int64.Type}, {"Asset", type text}, {"Asset Type", type text}, {"Subsidiary", type text}, {"Transaction Amount", type number}, {"Depreciation Posting Reference", type text}, {"Date Created", type datetime}, {"Depreciation Date", type date}, {"Depreciation Account", type text}, {"Period", type date}})
in
    #"Changed Type"<

 

[MarcelBeug]

My recommendation is to create a parameter record in Power Query from the parameter table (or range) in Excel, instead of a parameter table with a function.
This way, you can use a parameter in your queries just by referring to the parameter record and the field name.

Example code how to create such a parameter record:

let
    Source = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    Typed = Table.TransformColumnTypes(PromotedHeaders,{{"Parameter", type text}, {"Value", Int64.Type}}),
    Renamed = Table.RenameColumns(Typed,{{"Parameter", "Name"}}),
    RecordFromTable = Record.FromTable(Renamed)
in
    RecordFromTable

Some explanation in this video: