Forum Discussion
Need a simple query
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 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 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"