MyTetra Share
Делитесь знаниями!
Время создания: 14.07.2020 07:02
Текстовые метки: fnGetParameter, Power Query
Раздел: !Закладки - MSO - Excel - Power Query

Parameter_Id

Parameter_Name

Parameter_Value

1

1

List_UserName

Имя пользователя

2

FilePath

=ЗАМЕНИТЬ(D3;ПОИСК("USER_NAME";D3;1);9;List_UserName)

Путь к файлу источнику

3

FileTable

z_......

4

Y_Start

2020

5

Y_End

2020

6

M_Start

7

7

M_End

7

8

D_Start

10

9

D_End

13


mytetra://note/1594707643s97g2u7mkd



#"qqqqq" = Table.SelectRows(#"Замененное значение", each [Date_] >= #date(

fnGetParameter("Y_Start"),

fnGetParameter("M_Start"),

fnGetParameter("D_Start")

)

and [Date_] <= #date(

fnGetParameter("Y_End"),

fnGetParameter("M_End"),

fnGetParameter("D_End")

)),


// fnGetParameter function written by Ken Puls, FCPA, FCMA, MS MVP (Excel)

// Original source can be found at the following URL:

// http://www.excelguru.ca/blog/2014/11/26/building-a-parameter-table-for-power-query/


// To use the function you must have a table called "Parameters" in your workbook

// The header row must contain a "Parameter" and a "Value" column (with those names)


// Data rows should list the paramter name in the first column and the value of that

// parameter in the second column


// Provided you save this query under the name "fnGetParameter", you can then reference

// it from other queries as follows:

//

// =fnGetParameter("your_parameter_name")


// One useful sample to return the current folder:

// Parameter Name: File Path

// Parameter Value: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

// Call as "fnGetParamater("File Path")


(ParameterName as text) =>

let

ParamSource = Excel.CurrentWorkbook(){[Name="Parameters_tbl"]}[Content],

ParamRow = Table.SelectRows(ParamSource, each ([Parameter_Name]=ParameterName)),

Value=

if Table.IsEmpty(ParamRow)=true

then null

else Record.Field(ParamRow{0},"Parameter_Value")

in

Value

 
MyTetra Share v.0.53
Яндекс индекс цитирования