cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Need a simple query

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
Highlighted
Community Support
Community Support

Re: Need a simple query

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

Highlighted
Helper III
Helper III

Re: Need a simple query

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

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.