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

[Anonymous]

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;

[watkinnc]

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

--Nate

[cpwebb]

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]

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]

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]

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]

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]

>>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]

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 · Accepted solution]

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]

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

[watkinnc]

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

[Anonymous]

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