Forum Discussion
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"
Hi Anonymous ,
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Anonymous ,
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 ZhiIf 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"