MyTetra Share
Делитесь знаниями!
Время создания: 14.07.2020 07:02
Текстовые метки: fnGetParameter, Power Query
Раздел: !Закладки - MSO - Excel - Power Query
Запись: xintrea/mytetra_db_adgaver_new/master/base/15946993499vzwds8g7p/text.html на raw.githubusercontent.com

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.60
Яндекс индекс цитирования