Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @laciodrom_80,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
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
Thanks @v-jiascu-msft for the hint.
What do you mean about "it will be complicated in the production environment" ?
Best Regards
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |