cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
LoremIpsum
Regular Visitor

Importing dynamic named worksheets

I've developed many solutions in Excel with VBA years ago, and recently I've come back to it, but I see I have a lot to catch up on. I hope this is the right venue for my question. Please recommend a better place if this isn't it.

 

I have a situation where a data source (xlsx) file has a changing sheet name. The data I need to import is always on sheet1 but it's never named the same. Surprisingly this doesn't appear to be a common problem, because I had to search deep for a solution. 

 

FirstSheet = Table.SelectRows(Source, each [Kind] = "Sheet"){0}[Data],

It works, but because the end product's data is used real time, I'm hesitant to use it without first understanding it. My query has this:

 

DynamicSource = qryParameters("HomeboundPath"),
Source = Excel.Workbook(File.Contents(DynamicSource), null, true),
#"daily count_Sheet" = Source{[Item="111111",Kind="Sheet"]}[Data],

I guess it's the "table.selectrows" that concerns me, because I don't understand it's purpose, and I don't know where to go to learn it. I feels like I should be able to combine the "each [Kind] = "Sheet"){0}" into my existing query. No?

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community Support

Hi @LoremIpsum,

 

The query FirstSheet = Table.SelectRows(Source, each [Kind] = "Sheet"){0}[Data], will filter the Source table firstly based on the condition [Kind] column has "Sheet" value, then retrieve data from the first row of [Data] column. You can see below sample:

 

Source tableSource tableValues from Source table first row of [Data] column when [Kind] has "Sheet" valueValues from Source table first row of [Data] column when [Kind] has "Sheet" value

 

In your scenario, assume there is only one sheet (contains two columns) in the Excel file, and this sheet name is changed dynamically. You can write the Power Query like below:

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\<user name>\Desktop\New Microsoft Excel Worksheet.xlsx"), null, true),
    #"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column2"}, {"Data.Column1", "Data.Column2"})
in
    #"Expanded Data"

 

As above query doesn't specify sheet name, though the Excel sheet name is changed, the above query can always get data.

 

Best Regards,
QiuyunYu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-qiuyu-msft
Community Support
Community Support

Hi @LoremIpsum,

 

The query FirstSheet = Table.SelectRows(Source, each [Kind] = "Sheet"){0}[Data], will filter the Source table firstly based on the condition [Kind] column has "Sheet" value, then retrieve data from the first row of [Data] column. You can see below sample:

 

Source tableSource tableValues from Source table first row of [Data] column when [Kind] has "Sheet" valueValues from Source table first row of [Data] column when [Kind] has "Sheet" value

 

In your scenario, assume there is only one sheet (contains two columns) in the Excel file, and this sheet name is changed dynamically. You can write the Power Query like below:

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\<user name>\Desktop\New Microsoft Excel Worksheet.xlsx"), null, true),
    #"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column2"}, {"Data.Column1", "Data.Column2"})
in
    #"Expanded Data"

 

As above query doesn't specify sheet name, though the Excel sheet name is changed, the above query can always get data.

 

Best Regards,
QiuyunYu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hey @v-qiuyu-msft ,

 

This is an elegant solution and can be use for a small table wth a few columns as in this example.

But how can I make this work, if my table has over 100 columns? Can the a dynamic of list of columns be supplied where you have supplied the list of columns?

Any help appreciated.

Thank you for replying. I thought "FirstSheet = Table.SelectRows(Source, each [Kind] = "Sheet"){0}[Data]" was filtering of some sort, but since none of my columns contain the sheet name I was lost how it did that and worked. I still am. 

 

I understand the concept of your solution, but I'm not comfortable enough with the language to fit it into my existing design. I'm completely shocked that my problem isn't a common problem with a solid straight forward solution.


Where is the best source for learning Power Query's language?

Hi @LoremIpsum,

 

Is there any concern about the solution posted in my previous reply? Maybe you can share how the Excel workbook looks like and desired data in Power BI, so we can check if there any better solution?

 

Regarding the resource to learn Power Query, you can refer to this thread. Besides, @ImkeF and @MarcelBeug are good at Power Query, you can keep an eye on their posts in the forum.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-qiuyu-msft My concern is partially my lack of understand of the query language, and another part confusion that action on the data has to take place to "reference" the sheet, when it would make so much sense to be able to simply reference a sheet index (but you apparently can't).

Hi @LoremIpsum,

let me try a different explanation why your formula works like you want it:

 

PBIFilterSelections.jpg

 

1) Filters the table for those rows, who have "Sheet" in column "Kind". So if you have other object types like tables or named ranges, you assure this way that they won't interfere

2) {0} selects one row from your table and returns it as a record. As M starts to count at zero, this actually fetches the first row.

3) [Data] selects the column "Data" and as you're on the record-level already, you will directly receive the table from that cell.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeF Thank you for the education. The code wasn't executing like I thought it was, and your explanation helped clear that up. 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.