Forum Discussion
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 usi...
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-6c94334e492c)? 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]
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?
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