PQ download data from excel sheet dynamically

[Anonymous]

Hi,

I have just started using PQ in excel and cannot find a solution to this problem. I download the same set of data each month from 1 excel file (source) to another (destination). Problem is that data in source file are each month on the new sheet -> named Jan, Feb, Mar etc. Is there any way to set name of the worksheet with data dynamically?

Perfect solution for me would be to use some function that would take the name of the sheet from excel destinatnion file eg. cell A1 = Jan. I use VBA to automatically refresh all the queries I am using in the destination file so don't want to do a new PQ each month. Or if it is not possible to use function, is there a way to have a Pop-up window during refreshing the query that would ask me to select the sheet with data? One more solution on my mind - the sheet with the data I want is always the last one. Is there any way to set the query to extract the data from the last sheet? I hope I explained the problem well. Thanks for any ideas and your help.

[smpa01 · Accepted solution]

@Anonymous if you always want PQ to take the data only from the last sheet in an excel you can do the following

let
    Source = Excel.Workbook(File.Contents("C:\Users\X\Desktop\exceltest.xlsx"), null, true),
    X = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Filtered Rows" = Table.SelectRows(X, each [Index] = List.Max(X[Index]))
in
    #"Filtered Rows"

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. 

Here are the m codes in 'Advanced Editor'.

let
    Source = Excel.Workbook(File.Contents(YourPath), null, true),
    Custom1 = let 
  tab = Table.AddColumn( Table.SelectRows(Source,each [Kind]="Sheet"),"New",each if 
  [Item]="Jan" then 1 
  else if [Item]="Feb" then 2 
  else if [Item]="Mar" then 3
  else if [Item]="Apr" then 4
  else if [Item]="May" then 5
  else if [Item]="Jun" then 6
  else if [Item]="Jul" then 7
  else if [Item]="Aug" then 8
  else if [Item]="Sep" then 9
  else if [Item]="Oct" then 10
  else if [Item]="Nov" then 11
  else if [Item]="Dec" then 12
  else null 
  ),
  res=Table.Max(tab,"New")[Data]
in 
res,
    #"Promoted Headers" = Table.PromoteHeaders(Custom1, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Age", Int64.Type}, {"Salary", Int64.Type}})
in
    #"Changed Type"

Result:

Best Regards

Allan

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

[Anonymous]

Hi,

found out that an enhancement of this soultion will be helpful. It happens that I still need data for eg. Feb, but others already started filling in Mar data already.  This code will give me Max  data sheet - in this case Mar. Is there any way to add parameter function, that will choose month I want? I tried to use your solution and update it at least with the following below, but it is not working.

res=Table.Max(tab,"New")[Data] -> I changed it to res=Table.MaxN(tab, "New", each [New] =2)  

potentially each [New] = x  where x=fnSALES 

This results in an Expression.error-> we cannot convert a value of type List to type Table. Any ideas how to get it work?

Meanwhile I created function fnSALES, where I can input variable directly in an excel sheet.  Would be nice to incorporate the function into the query. I add the code for function, just to make sure it is set correctly as value not text etc. Value of this function may be e.g. Feb or 2 if it makes any difference in the solution. Code of function below:

let
ParamSource = Excel.CurrentWorkbook(){[Name="SALES"]}[Content],
ParamRow = Table.SelectRows(ParamSource, each ([Input] = "Sheet")),
Value=
if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},"Value")
in
Value

Any suggestions? Thanks for ideas.

Iva

[smpa01 · Accepted solution]

@Anonymous if you always want PQ to take the data only from the last sheet in an excel you can do the following

let
    Source = Excel.Workbook(File.Contents("C:\Users\X\Desktop\exceltest.xlsx"), null, true),
    X = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Filtered Rows" = Table.SelectRows(X, each [Index] = List.Max(X[Index]))
in
    #"Filtered Rows"

[Anonymous]

Hi,

I tried your solution and when I made some adjustments it worked well. The workbook contains also hidden sheets and Int64.Type was not working. So I simply deleted it and also used a filter for [Kind]. I tried to enhance it with a function for selecting exact month number. Seems it works.

let
Source = Excel.Workbook(File.Contents("xxxxxxxx1.xlsx"), null, true),

mth = Decimal.From(fnSALES),

X = Table.AddIndexColumn(Table.SelectRows(Source,each [Kind]="Sheet"), "Index", 1, 1),

res = Table.SelectRows(X, each [Index] = mth) [Data],
res1 = res{0},
#"Changed Type" = Table.TransformColumnTypes(res1,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, }),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
#"Removed Blank Rows" = Table.SelectRows(#"Promoted Headers", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Filtered Rows" = Table.SelectRows(#"Removed Blank Rows", each ([#"FLEET"] <> null))
in
#"Filtered Rows"

Not sure how to get rid of res and res1, but I works, so I'll leave it as it is.