Convert a Table to Text String to Use in Import Query

[mm5308]

It took me a while to get to this point; now I'm stuck. Any help would be greatly appreciated!

Big picture, I'm trying to create an Import query to grab targeted data from an SSAS database since I don't need everything in it via a Live connection. The tricky part is that for some of the filters in the Import query, I want to be able to reference a table that I've created so those filters can be adjusted.

For example, filtering by year. I'll start small, with only 1 year. 

Here would be a static query:

let
Source = AnalysisServices.Database("[DB Address]", "[DB Name]", [Query="EVALUATE#(lf)SUMMARIZECOLUMNS(#(lf) 'Calendar'[Year],#(lf) KEEPFILTERS( TREATAS( {""2019""}, 'Calendar'[Year] ))#(lf))#(lf)ORDER BY #(lf) 'Calendar'[Year] ASC", Implementation="2.0"])

in
Source

Instead, I was able to create a dynamic version by splitting the Query= step as follows:

let
Q1 = "EVALUATE#(lf)SUMMARIZECOLUMNS(#(lf) 'Calendar'[Year],#(lf) KEEPFILTERS( TREATAS( ",
Year = Text.Combine({"{""", Text.From(Date.Year(DateTime.LocalNow())-3), """}"}),
Q2 = ", 'Calendar'[Year] ))#(lf))#(lf)ORDER BY #(lf) 'Calendar'[Year] ASC",
QF = Text.Combine({Q1, Year, Q2}),
Source = AnalysisServices.Database("[DB Address]", "[DB Name]", [Query=QF, Implementation="2.0"])

in
Source

Now the tricky part comes in. I would like for this filter to be based on a table so it can include several years - which is where I'm having trouble. How can I create a text string from a table that will fit into the formula above?

For instance, I have a table with several years:

(Table = YearsList)

Year

2019

2020

2021

I was able to convert that table into a list like this:

let
Source = Table.ToList(YearsList, Combiner.CombineTextByDelimiter(","))
in
Source

However, I keep getting an error message:

Expression.Error: We cannot convert a value of type List to type Text. Details:

Value=[List]

Type=[Type]

I'm guessing the error has something to do with formatting. In the Native Query (hopefully I'm uisng the right terminology), the years would need to be formatted as follows:

...KEEPFILTERS( TREATAS( {""2019"", ""2020"", ""2021""}, 'Calendar'[Year] ))

I'm afraid that the function Table.ToList I'm using does not convert the quote marks and curly brackets appropriately...?

Does anyone know a way to achieve this? If you have suggestions for a different route, I'm open to that too.

(Keep in mind that I am strictly looking for a way to reference an actual table. I realize that I could take the Date.Year...-3 section of the formula that already works and add in two extra clauses for Date.Year...-2 and Date.Year...-1. But I would prefer a table so I can manually change values as needed since Years is not the only filter I want to use this for.)

[talbot · Accepted solution]

You need to converrt your table to text like this:

let
Source = "{" & Text.Combine(Table.TransformColumns(YearList,{"Year", each """""" & Text.From(_) & """"""})[Year],",") & "}"
in
Source

[mm5308]

@talbot and @wdx223_Daniel,

Thank you both!! Although different, each method worked so I'll accept them both as solutions...with one caveat related to the syntax.

In each proposal, Power Query returned an error until took out 2 quotation marks as follows:

• @talbot, in both places with 6 quotation marks (""""""), I reduced the number to 4.
• @wdx223_Daniel, in the two places with the sequence "{""""" I changed it to "{""" (i.e. 3 quotation marks after the curly brackets instead of 5), and in the two places with 5 standalone quotation marks I reduced the number to 3.

Thanks again for your replies! I'm still new to the M language so not only are these solutions helpful for the problem at hand, they also give me insight into the syntax and where I need to study more.

[wdx223_Daniel · Accepted solution]

Year= "{"""""&Text.Combine(List.Transform(YearsList[Year],Text.From),""""",""""")&"""""}"

[talbot · Accepted solution]

You need to converrt your table to text like this:

let
Source = "{" & Text.Combine(Table.TransformColumns(YearList,{"Year", each """""" & Text.From(_) & """"""})[Year],",") & "}"
in
Source