Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Getting data from current sheet not working with excel.CurrentWorkbook()

Hello, I am trying to use power query to create a table from multiple sheets in a workbook, and the end goal is for my colleagues to be able to use it themselves as well, so in the query, I tried using excel.CurrentWorkbook as the source, but it will give this error. I do not really understand what I am doing wong as it is supposedly pointing to the existing table, so if you can help me, it would be much appreciated!

 

 

---------- Message ----------
[Expression.Error] The key didn't match any rows in the table.

---------- Session ID ----------
07c2dca4-458c-4f68-8d60-5edc4c44cc7d

---------- Mashup script ----------
section Section1;
shared MainRaw = let
  Source = Excel.CurrentWorkbook(),
  Navigation = Source{[Item = "MainRaw", Kind = "Sheet"]}[Data],
  #"Promoted headers" = Table.PromoteHeaders(Navigation, [PromoteAllScalars = true]),
  #"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"Source", type text}, {"Week Of", type date}, {"Sub Tactic", type text}, {"Market (New)", type text}, {"Tactic.", type text}, {"T1 Month", Int64.Type}, {"Month", Int64.Type}, {"Date + T-1", type date}, {"Comparison", type text}, {"Week (Monday First)", type text}, {"Brand", type text}, {"OS (Ad Set)", type text}, {"Market (New)2", type text}, {"Tactic (New)", type text}, {"Campaign ID", Int64.Type}, {"Campaign", type text}, {"Ad Set ID", Int64.Type}, {"Ad Set", type text}, {"Date", type date}, {"Spend", type number}, {"FB Installs (28 Day click + 1 Day View)", Int64.Type}, {"CPA: Mobile App Installs", type number}, {"Installs (Branch + SKAN) ", Int64.Type}, {"Branch/FB Installs", type number}, {"Purchases (App) (28 Day Click + 1 day View)", Int64.Type}, {"Revenue - Android (28 Day click + 1 Day View)", type number}, {"MULTIPLIER", type number}, {"24m LTV (28 Day click + 1 Day View)", type number}, {"24m ROAS (28 Day click + 1 Day View)", type number}, {"Impressions", Int64.Type}, {"Link Clicks", Int64.Type}, {"CTR (Link Click-Through Rate)", type number}, {"CTI (Android FB Installs/Link Clicks)", type number}, {"CPM", type number}, {"fb_mobile_initiated_checkout (App)", Int64.Type}, {"fb_mobile_content_view (App)", Int64.Type}, {"fb_mobile_search (App)", Int64.Type}, {"Conversion Event", type text}, {"T-1 Installs", type any}, {"T-1 24m LTV", type any}})
in
  #"Changed column type";
shared #"T-1 Raw" = let
  Source = Excel.Workbook(File.Contents("/Users/pdemirci/Desktop/EG MAI T-1 Daily Report.xlsx"), null, true),
  Navigation = Source{[Item = "T-1 Raw", Kind = "Sheet"]}[Data],
  #"Promoted headers" = Table.PromoteHeaders(Navigation, [PromoteAllScalars = true]),
  #"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"Source", type text}, {"Week Of", type date}, {"Sub Tactic", type text}, {"Market (New)", type text}, {"Tactic.", type text}, {"T1 Month", Int64.Type}, {"Month", Int64.Type}, {"Date + T-1", type date}, {"Comparison", type text}, {"Week (Monday First)", type text}, {"Brand", type text}, {"OS (Ad Set)", type text}, {"Market (New)2", type text}, {"Tactic (New)", type text}, {"Campaign ID", Int64.Type}, {"Campaign", type text}, {"Ad Set ID", Int64.Type}, {"Ad Set", type text}, {"Date", type date}, {"Spend", type any}, {"FB Installs (28 Day click + 1 Day View)", type any}, {"CPA: Mobile App Installs", type any}, {"Installs (Branch + SKAN) ", type any}, {"Branch/FB Installs", type any}, {"Purchases (App) (28 Day Click + 1 day View)", type any}, {"Revenue - Android (28 Day click + 1 Day View)", type any}, {"MULTIPLIER", type any}, {"24m LTV (28 Day click + 1 Day View)", type any}, {"24m ROAS (28 Day click + 1 Day View)", type any}, {"Impressions", type any}, {"Link Clicks", type any}, {"CTR (Link Click-Through Rate)", type any}, {"CTI (Android FB Installs/Link Clicks)", type any}, {"CPM", type any}, {"fb_mobile_initiated_checkout (App)", type any}, {"fb_mobile_content_view (App)", type any}, {"fb_mobile_search (App)", type any}, {"Conversion Event", type any}, {"T-1 Installs", Int64.Type}, {"T-1 24m LTV", type number}})
in
  #"Changed column type";
shared Append = let
  Source = Table.Combine({#"T-1 Raw", MainRaw})
in
  Source;

 

 

1 ACCEPTED SOLUTION
cpwebb
Microsoft Employee
Microsoft Employee

Is that table actually formatted as an Excel table (see https://support.microsoft.com/en-us/office/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e...)? That should be your first step. Once you have done that you will be able to connect to it using Excel.CurrentWorkbook(). For example, if you have a table in your workbook called MyTable, the following M code will return the data from it:
Excel.CurrentWorkbook(){[Name="MyTable"]}[Content]

View solution in original post

12 REPLIES 12
watkinnc
Super User
Super User

Are you sure that [Item ="Sheet"] in your navigation step is correct? Do you have a sheet that is named "Sheet"?

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
cpwebb
Microsoft Employee
Microsoft Employee

Can you create a new query that gets data from that sheet though? If you can, and it works, can you open the Advanced Editor in the Power Query Editor and post the M code here?

cpwebb
Microsoft Employee
Microsoft Employee

Did you change the name of the worksheet with the data on? From your code it looks like Power Query is looking for sheets called MainRaw and T-1 Raw.

 

See https://blog.crossjoin.co.uk/2020/04/06/understanding-the-the-key-didnt-match-any-rows-in-the-table-... for more detail on this kind of problem.

Anonymous
Not applicable

Thanks a lot for the reply, no I did not, so the sheet names are the same and they are not deleted either, I checked out this page, but since I made no changes, it is not solving that issue currently. 

cpwebb
Microsoft Employee
Microsoft Employee

Is the case the same though? Power Query is case sensitive, so "MainRaw" is not the same as "Mainraw". If that's not the problem, can you try creating a new Power Query query that connects to your current workbook and if that works, post the code here?

Anonymous
Not applicable

So I created a new sheet in my workbook and then created a connection to it, using the PowerQuery UI, the code is as follows, and except for the name and fields of the shield, it is identical to what I was working with when I got the error I posted above:

let
  Source = Excel.Workbook(File.Contents("/Users/pdemirci/Desktop/EG MAI T-1 Daily Report.xlsx"), null, true),
  Navigation = Source{[Item = "Sheet", Kind = "Sheet"]}[Data],
  #"Promoted headers" = Table.PromoteHeaders(Navigation, [PromoteAllScalars = true]),
  #"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"Date", type date}, {"Quantity", Int64.Type}, {"Product", type text}})
in
  #"Changed column type"

Once again, then as I did before I change the code to have the source as current workbook as follows:

let
  Source = Excel.CurrentWorkbook(),
  Navigation = Source{[Item = "Sheet", Kind = "Sheet"]}[Data],
  #"Promoted headers" = Table.PromoteHeaders(Navigation, [PromoteAllScalars = true]),
  #"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"Date", type date}, {"Quantity", Int64.Type}, {"Product", type text}})
in
  #"Changed column type"

I get the same error as a result: 

---------- Message ----------
[Expression.Error] The key didn't match any rows in the table.

---------- Session ID ----------
a2e4f2fe-afad-4939-bcf6-3977cc31da46

---------- Mashup script ----------
section Section1;
shared MainRaw = let
  Source = Excel.Workbook(File.Contents("/Users/pdemirci/Desktop/EG MAI T-1 Daily Report.xlsx"), null, true),
  Navigation = Source{[Item = "MainRaw", Kind = "Sheet"]}[Data],
  #"Promoted headers" = Table.PromoteHeaders(Navigation, [PromoteAllScalars = true]),
  #"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"Source", type text}, {"Week Of", type date}, {"Sub Tactic", type text}, {"Market (New)", type text}, {"Tactic.", type text}, {"T1 Month", Int64.Type}, {"Month", Int64.Type}, {"Date + T-1", type date}, {"Comparison", type text}, {"Week (Monday First)", type text}, {"Brand", type text}, {"OS (Ad Set)", type text}, {"Market (New)2", type text}, {"Tactic (New)", type text}, {"Campaign ID", Int64.Type}, {"Campaign", type text}, {"Ad Set ID", Int64.Type}, {"Ad Set", type text}, {"Date", type date}, {"Spend", type number}, {"FB Installs (28 Day click + 1 Day View)", Int64.Type}, {"CPA: Mobile App Installs", type number}, {"Installs (Branch + SKAN) ", Int64.Type}, {"Branch/FB Installs", type number}, {"Purchases (App) (28 Day Click + 1 day View)", Int64.Type}, {"Revenue - Android (28 Day click + 1 Day View)", type number}, {"MULTIPLIER", type number}, {"24m LTV (28 Day click + 1 Day View)", type number}, {"24m ROAS (28 Day click + 1 Day View)", type number}, {"Impressions", Int64.Type}, {"Link Clicks", Int64.Type}, {"CTR (Link Click-Through Rate)", type number}, {"CTI (Android FB Installs/Link Clicks)", type number}, {"CPM", type number}, {"fb_mobile_initiated_checkout (App)", Int64.Type}, {"fb_mobile_content_view (App)", Int64.Type}, {"fb_mobile_search (App)", Int64.Type}, {"Conversion Event", type text}, {"T-1 Installs", type any}, {"T-1 24m LTV", type any}})
in
  #"Changed column type";
shared #"T-1 Raw" = let
  Source = Excel.Workbook(File.Contents("/Users/pdemirci/Desktop/EG MAI T-1 Daily Report.xlsx"), null, true),
  Navigation = Source{[Item = "T-1 Raw", Kind = "Sheet"]}[Data],
  #"Promoted headers" = Table.PromoteHeaders(Navigation, [PromoteAllScalars = true]),
  #"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"Source", type text}, {"Week Of", type date}, {"Sub Tactic", type text}, {"Market (New)", type text}, {"Tactic.", type text}, {"T1 Month", Int64.Type}, {"Month", Int64.Type}, {"Date + T-1", type date}, {"Comparison", type text}, {"Week (Monday First)", type text}, {"Brand", type text}, {"OS (Ad Set)", type text}, {"Market (New)2", type text}, {"Tactic (New)", type text}, {"Campaign ID", Int64.Type}, {"Campaign", type text}, {"Ad Set ID", Int64.Type}, {"Ad Set", type text}, {"Date", type date}, {"Spend", type any}, {"FB Installs (28 Day click + 1 Day View)", type any}, {"CPA: Mobile App Installs", type any}, {"Installs (Branch + SKAN) ", type any}, {"Branch/FB Installs", type any}, {"Purchases (App) (28 Day Click + 1 day View)", type any}, {"Revenue - Android (28 Day click + 1 Day View)", type any}, {"MULTIPLIER", type any}, {"24m LTV (28 Day click + 1 Day View)", type any}, {"24m ROAS (28 Day click + 1 Day View)", type any}, {"Impressions", type any}, {"Link Clicks", type any}, {"CTR (Link Click-Through Rate)", type any}, {"CTI (Android FB Installs/Link Clicks)", type any}, {"CPM", type any}, {"fb_mobile_initiated_checkout (App)", type any}, {"fb_mobile_content_view (App)", type any}, {"fb_mobile_search (App)", type any}, {"Conversion Event", type any}, {"T-1 Installs", Int64.Type}, {"T-1 24m LTV", type number}})
in
  #"Changed column type";
shared Append = let
  Source = Table.Combine({#"T-1 Raw", MainRaw})
in
  Source;
shared Sheet = let
  Source = Excel.CurrentWorkbook(),
  Navigation = Source{[Item = "Sheet", Kind = "Sheet"]}[Data],
  #"Promoted headers" = Table.PromoteHeaders(Navigation, [PromoteAllScalars = true]),
  #"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"Date", type date}, {"Quantity", Int64.Type}, {"Product", type text}})
in
  #"Changed column type";

 Let me know if maybe I am using it incorrectly

cpwebb
Microsoft Employee
Microsoft Employee

>>I change the code to have the source as current workbook as follows:

 

OK, I see the problem now. The Excel.CurrentWorkbook function only works with tables and named ranges. It cannot connect to worksheets, which is why your code is failing.

Anonymous
Not applicable

Ah understood, so is it not possible to to point to a table in this sheet then, which is what I want to do. There is a single table in my sheet, and I would like to use that single table as a source, and later append it to another table from another sheet. The link here mentions what you are saying, but I am afraid I am still lost about how I can point to that table so that the file will allow my colleagues to just change the data in table when they want, keeping the field names the same and update it. 

cpwebb
Microsoft Employee
Microsoft Employee

Is that table actually formatted as an Excel table (see https://support.microsoft.com/en-us/office/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e...)? That should be your first step. Once you have done that you will be able to connect to it using Excel.CurrentWorkbook(). For example, if you have a table in your workbook called MyTable, the following M code will return the data from it:
Excel.CurrentWorkbook(){[Name="MyTable"]}[Content]

Anonymous
Not applicable

I was able to do it using this, thanks a million for the help! 

You could try not specifying a sheet at all, and just go Excel.CurrentWorkbook())[Data]

That should teyurn a list of tables/sheets from the workbook. 

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Anonymous
Not applicable

It is the same case, there is no difference at all. The sheet name is MainRaw. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.