Forum Discussion

kadapavel's avatar
kadapavel
Helper II
8 years ago

Need help with functions / parameters

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

 

Need Help

3 Replies

  • nickchobotar's avatar
    nickchobotar
    Skilled Sharer
    8 years ago

    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's avatar
      kadapavel
      Helper II
      8 years ago

      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