CODEWITHSUNDEEP
excelpowerquerym
Spencer
Spencer

Reputation: 457

PowerQuery refer to workbook name

The following code is to simply extract all columns with "Spencer" in column A from the source. The workbook name itself is Spencer.xlsx. So I'd like to do something like each ([Column1] = This.Workbook.Name)), but I don't know what the syntax should be in the PowerQuery language. 

let
    Source = Excel.Workbook(File.Contents("C:\Users\spencer\Documents\Source.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] = "Spencer"))
in
    #"Filtered Rows"

Upvotes: 1

Views: 3533

Answers (1)

horseyride
horseyride

Reputation: 21318

Create a formula in a cell in excel workbook that returns the current filename, and give that cell a range name, here "TheFilename" as an example

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND(".xl",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

Refer to that range name in powerquery using syntax below, which you can paste into powerquery using Home... Advanced Editor

name = Excel.CurrentWorkbook(){[Name="TheFilename"]}[Content]{0}[Column1]

then refer to it in your filter like this

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] = name))

Upvotes: 1

Related Questions