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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cbtekrony
Resolver I
Resolver I

Need a simple query

I'm new to queries, is there a basic, simple query I can write to access a sheet from several workbooks and upload them into one? I just need the sheet "Data" to upload from several excel workbooks. I cannot seem to get it to work. I'm getting: Expression_/SyntaxError:Token Identifier expected. 

Here is my query:

let
Source = Folder.Files("C:\Users\cxxxxxy\OneDrive - xxxxx\Power BI\NewSalesC"), null, true),
filtered_for_xls = Table.SelectRows(Source, each Text.StartsWith([Extension], ".xls")),
added_exceldata = Table.AddColumn(filtered_for_xls, "ExcelData", each Excel.Workbook([Content])),
expanded_exceldata = Table.ExpandTableColumn(added_exceldata, "ExcelData", {"Name", "Data", "Kind"}, {"Name.1", "Data", "Kind"}),
filtered_for_datasheets = Table.SelectRows(expanded_exceldata, each ([Kind] = "Sheet") and ([Name.1] = "Data" or [Name.1] = "data" or [Name.1] = "Sheet1")),
expanded_data = Table.ExpandTableColumn, ({"CustID", "Effective Date", "Sales Period", "Customer", "Item/Product", "Product", "Appl/Use", "Product-Reporting", "Fee Type", "Bonus Category", "1st Year Value", "TSV", "RSM", "Market", "State", "Channel", "SalesKey"}, {"CustID", "Effective Date", "Sales Period", "Customer", "Item/Product", "Product", "Appl/Use", "Product-Reporting", "Fee Type", "Bonus Category", "1st Year Value", "TSV", "RSM", "Market", "State", "Channel", "SalesKey"}),
PromoteNames = Table.PromoteHeaders(RemoveCols, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CustID", Int64.Type}, {"Effective Date", type date}, {"Sales Period", type date}, {"Customer", type text}, {"Item/Product", type text}, {"Product", type text}, {"Product-Reporting", type text}, {"Appl/Use", type text}, {"Fee Type", type text}, {"Bonus Category", type text}, {"1st Year Value", type number}, {"TSV", Int64.Type}, {"RSM", type text}, {"Market", type text}, {"State", type text}, {"Channel", type text}, {"SalesKey", Int64.Type}, {"Sales Product Summary", type text}})
in
#"Changed Type"

1 ACCEPTED SOLUTION
dax
Community Support
Community Support

Hi @cbtekrony , 

Which steps will prompt this error. You could check whether there is something wrong in reference(you could check Table.SelectRows(expanded_exceldata, each ([Kind] = "Sheet") and ([Name.1] = "Data" or [Name.1] = "data" or [Name.1] = "Sheet1")), whether [Name.1] exist in each excel or sheet and the name is correct ). You could try Table.SelectRows(expanded_exceldata, each ([Kind] = "Sheet")) to see whether it work or not.

Best Regards,
Zoe Zhi

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

2 REPLIES 2
dax
Community Support
Community Support

Hi @cbtekrony , 

Which steps will prompt this error. You could check whether there is something wrong in reference(you could check Table.SelectRows(expanded_exceldata, each ([Kind] = "Sheet") and ([Name.1] = "Data" or [Name.1] = "data" or [Name.1] = "Sheet1")), whether [Name.1] exist in each excel or sheet and the name is correct ). You could try Table.SelectRows(expanded_exceldata, each ([Kind] = "Sheet")) to see whether it work or not.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @dax ,

That was helpful. Since I posted this I also did some modifications. One of my problems is that I did not drilldown to the data either. Here is the query I ended up with. 

 

let
Source = Folder.Files("C:\Users\cxxxy\OneDrive\Power BI\NewSalesC"),
filtered_for_xls = Table.SelectRows(Source, each Text.StartsWith([Extension], ".xls")),
added_exceldata = Table.AddColumn(filtered_for_xls, "ExcelData", each Excel.Workbook([Content])),
expanded_exceldata = Table.ExpandTableColumn(added_exceldata, "ExcelData", {"Name", "Data", "Kind"}, {"Name.1", "Data", "Kind"}),
filtered_for_datasheets = Table.SelectRows(expanded_exceldata, each ([Kind] = "Sheet") and ([Name.1] = "Data" )),
promoted_headers = Table.AddColumn(filtered_for_datasheets, "PromoteHeaders", each Table.PromoteHeaders([Data])),
Data1 = promoted_headers{0}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Data1, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Sales Period", type date}, {"Customer", type text}, {"TSV", type number}, {"Fee Type", type text}, {"State", type text}, {"Product Name", type text}, {"Product", type text}, {"Market", type text}, {"Appl/Use", type text}, {"Change Reason", type text}, {"Channel", type text}, {"Product Category", type text}, {"Platform", type text}, {"Sales Product Summary", type text}})
in
#"Changed Type"

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors