Need help with functions / parameters

[kadapavel]

Hello

I have data recording in Azure Data lake where for each day I have separate table.

In order to create report for some period of time I use advance query and combine data. Currently I use Rstudion which generates for me text query, which then I paste to Power bi advanced query editor.

Example:

let
	SignalCalendar = let
		Source1 = DataLake.Contents("https://xxx.azuredatalakestore.net/xxx/xxx/xxx/xxx/2017/07/19/xxx_Data_20170719.csv"),
		Content1 = Source1{[Name=""]}[Content],
		ImportedCSV1 = Csv.Document(Content1,[Delimiter=",", Columns=108, Encoding=1252, QuoteStyle=QuoteStyle.None]),
		Headers1 = Table.PromoteHeaders(ImportedCSV1,[PromoteAllScalars=true]),
		Source2 = DataLake.Contents("https://xxx.azuredatalakestore.net/xxx/xxx/xxx/xxx/2017/07/20/xxx_Data_20170720.csv"),
		Content2 = Source2{[Name=""]}[Content],
                ImportedCSV2 = Csv.Document(Content2,[Delimiter=",", Columns=108, Encoding=1252, QuoteStyle=QuoteStyle.None]),
                Headers2 = Table.PromoteHeaders(ImportedCSV2,[PromoteAllScalars=true]),
               in 
               Table.Combine({Headers1,Headers2})
		

After looking few introduction videos about parameter and functions I got hope that I can have a Power bi template with functions and parameters where user could set starting and end day and get query through power bi without using Rstudio help.

If there are any articles about it I would appreciate for any information.

Thanks in advance

[nickchobotar]

@kadapavel

I see you are combining two tables in your M script and my understanding is that you want to filter these combined tables by date. It's a two step process, you will need create Start and End data parameters then reference your parameters in Power Query against your data column.

you can follow the link below on how parameter are created.   

https://www.mssqltips.com/sqlservertip/4475/using-parameters-in-power-bi/

REF: "I have data recording in Azure Data lake where for each day I have separate table."   - That's pretty unusual architecture with individual table for each date - that must be  tough to manage. Do you mind if I ask why are you guys doing this?

N-

[kadapavel]

@nickchobotar

Thanks for reply!

Actually I'm combining more than 2 tables...

Currently data structure is 1 day  => one folder with one table in it.

Data is stored in Azure datalake and this data structure was chosen in order to have flexibility with quering different periods of time.

I can query for a single day(1 table), for 1 month(28-31 tables), of for a year... long periods require very long query text in advanced query editor. 

Way of quering is not elegant, but I have not found a way how to query for specific period in datalake.

Therefore I thought there could be an option to use parameters with functin and make automatic query for required period(tables).

If you know how to make query for a specific period(raws) in datalake, I could rebuild data and use only 1 table and then query for specific rows(starting date, ened date)

BR

Pavel

[nickchobotar]

@kadapavel

Have you thought about introducing a DATE table https://www.agilebi.com.au/blog/power-bi-date-dimension (related to your source tables model) ? You could do that and then use a slicer (with data field) in Power BI report view to return customer periods data. There is not need to build this logic in Power Query. 

N-