Forum Discussion
Multiple of same style report using csv strings in parameters for multiple customers
Hello,
We are building the same report layout for several of our customers & currently use a parameter for the api key.
But would like to know if it is possible to do the same with the csv string data sources for each customer as a parameter table or individual parameters also, instead of re-updating every query which is very time consuming. We have up to 16 report sources into each report.
Cheers
3 Replies
Excellent, thank you!
Happy Pi Day!- PiEye
Resolver II
Hahah! You're welcome and happy pi day too 🙂
- PiEye
Hi TillerTechnical I think I understand, you want to pass a value stored in a file to a parameter?
You can do this by loading the file data as normal, then using M Query List.Max() function to return a single scalar value.
For example - if we have a file with just one value in it, we can load it, then take the "max" to return a single value
MQuery code for this is:
let
Source = Csv.Document(File.Contents("<foldername>\One Row.csv"),[Delimiter=",", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Value"=List.Max(Source[Column1])
in
#"Value"You can see this is now returning a single value and the icon for the query has changed to indicate that it is no longer a table:
This value can now be referenced & passed to other queries, in the same way that you would use a parameter. Here, I'm able to reference it as a filter:
Multiple values in a csv
To create a parameter from a file with multiple values in it, and select one customer you can use the same logic but filter the loaded csv table before reducing to one value:
let
Source = Csv.Document(File.Contents("<foldername>\Several Rows.csv"),[Delimiter=" ", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Cust] = "B")),
Result=List.Max(#"Filtered Rows"[Key])
in
ResultHTH
Pi
