Trying to improve queryng dataset using M-language

[laciodrom_80]

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

[v-jiascu-msft]

Hi @laciodrom_80,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[laciodrom_80 · Accepted solution]

Hi @v-jiascu-msft,

solved using Merge Queries

Regards

[v-jiascu-msft]

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]

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]

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