Forum Discussion

laciodrom_80's avatar
laciodrom_80
Icon for Helper IV rankHelper IV
7 years ago
Solved

Trying to improve queryng dataset using M-language

Hi all,

 

I've got a dataset consisting of a sql db and an azure blob storage: in the sql db I've got a table Series containing some numeric series information such as Serie Name, Type, Granularity, ... while in the blob storage I've got a lot of csv files each containing series values, dates, and corresponding serie's name. Series values in csv files are linked with the corresponding record on sql db by Serie Name column in Series Table.

 

Series Table columns

Serie Name - Type - Granularity

 

Csv files columns

Serie Name - Value - Date

 

In my report I have to display data only for one serie typology so, in order to have a smaller dataset, I'd like to load from csv files only data about the series with the typology of interest (not all ones and then filtering them into the report by typology).

 

This is the query in M-language I use to get csv files in the blob storage directory, should I enrich it with a condition on Series table? Is it possible? 

 

= Table.SelectRows(dir, each Text.StartsWith([Name], "Dir/Default/REAL/"))

 

Thanks a lot in advance for any clue

Need Help
Tips and Tricks
Tutorial Requests

5 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Luca,

     

    This is possible. But it will be complicated in the production environment. Please refer to the M code below. The second one invokes the values from the first one. It could give you some ideas.

    //ColorsTable
    let Source = Csv.Document(File.Contents("D:\DataSource\a.csv"),[Delimiter=",", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]), 
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]) 
    in 
        #"Promoted Headers"
    let
    colors = ColorsTable[Color],
    Source = Sql.Database("dal6", "contosoretaildw", [Query="select * from dimproduct where colorname in ('" & Text.Combine(colors, "','") & "')"])
in
    Source

     

    Best Regards,
    Dale

    • laciodrom_80's avatar
      laciodrom_80
      Icon for Helper IV rankHelper IV
      7 years ago

      Thanks v-jiascu-msft for the hint.

      What do you mean about "it will be complicated in the production environment" ?

       

      Best Regards

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi Luca,

         

        There could be more situations to handle and more exceptions to catch. Some possibilities are as follows.

        1. More than one CSV files. You could combine them first.

        2. The blank values in the CSV files. 

        3. The performance. 

        In the test, I just use one file and one SQL query. Anyway, you can give it a try. I would appreciate it if you can share the results. 

         

        Best Regards,
        Dale