Getting data from Excel File for a variable in PowerQuery

[JulianC8]

Hi everyone,

I have a PowerBI File with a lot of diffent tables and queries. In one of these queries I would like to declare a variable called myVar, which contains a indefinite number of values separated by a semicolon. Example:

myVar = "Test1;Test2;Test3;Test4"

This variable is then further used in my query, which is not described in this post.

I now have an Excel File containing these values in one Column. Example:

Now my PowerQuery Question: How do i get the data of this Excel File into my PowerBI Data Model so that I can use it in a query as variable in the format as shown above?

I played around with parameters and combining data and so on but I did not come closer to a solution.

Any ideas?

BR

Julian

[jennratten · Accepted solution]

Hello @JulianC8 - you can use the Excel workbook connector (New Source > Excel Workbook) to connect to your Excel file and get you data.  Once you have the data from Excel you can drill down to the values which creates a list.  Then name the list myVar.

[JulianC8]

Even better! Thanks a lot!

[jennratten · Accepted solution]

Hello @JulianC8 - you can use the Excel workbook connector (New Source > Excel Workbook) to connect to your Excel file and get you data.  Once you have the data from Excel you can drill down to the values which creates a list.  Then name the list myVar.

[JulianC8]

Hi @jennratten 

Wow, it was really that easy. Thanks a lot!

One remark to round up the solution to my initial problem. In my query I now use the myVar and create a new column out if it (see step "AddCol"). After that I added the second line ExtractValues to get a list of values separated by Semicolons, as I wanted to have.

AddCol = Table.AddColumn(Source, "myColName", each myVar),
ExtractValues = Table.TransformColumns(AddCol , {"myColName", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),

BR

Julian

[jennratten · Accepted solution]

You're welcome!  If your intent is to add a new column with a list of values separated by a semi-colon, you would do it like this....

= Table.AddColumn(PreviousStep, "Custom", each Text.Combine ( myVar, ";" ), type text )