If statement not working in advanced editor

[shlobodon]

I am trying to create a dynamic workbook that will update data depending on which "Location" is selected from a drop down list. I was able to create it, but I want to take it a bit further.

I want the workbook to show all available data, not just data pertaining to a location, if the drop down list is blank. I am struggling with the if statement needed to create this. My code is below

let
Table2 = let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Location2 = Excel.CurrentWorkbook(){[Name="Location2"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Calendar month", Int64.Type}, {"Ship:Cust Bus Area 3", type text}, {"Ship:Cust Bus Area 1", type text}, {"Ship:Cust Bus Area 2", type text}, {"Ship:Cust Bus Area 4", type text}, {"Ship:Cust Bus Area 5", type text}, {"Receive Method", type text}, {"Prod Category", type text}, {"Material Type", type text}, {"Material Group", type text}, {"Product hier.lev. 1", type text}, {"VAS Code", Int64.Type}, {"Company code", Int64.Type}, {"Sales Amount", type number}, {"Sales Amount_1", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Ship:Cust Bus Area 3", "ShipCust Bus Area 3"}})
in
#"Renamed Columns",
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Location2 = Excel.CurrentWorkbook(){[Name="Location2"]}[Content],
#"idkwhatdoing" = if Excel.CurrentWorkbook(){[Name="Location2"]}[Content] = null then Table.SelectRows(Table2)
else Table.SelectRows(Table2, each ([ShipCust Bus Area 3] = Excel.CurrentWorkbook(){[Name="Location2"]}[Content]{0}[Column1]))
in
#"idkwhatdoing"

The if statement I am having issues with is in red. I believe there is some kind of format issue where even though the drop down list cell is blank, it is not being registered as null. Ironically, when the else is carried out, the same value not identified as null pulls null data from the data source.

 

[sandy666]

try = ""

 

[shlobodon]

should have mentioned i did try "". same results as using null

 

[sandy666]

your Location2 is a:
- named range
- table?

 

[shlobodon]

Location 2 is a single cell, the drop down cell. I named the single cell Location2

 

[sandy666]

could you post a link to the shared excel file with your data, via dropbox, onedrive, googledrive or any similar?

 

[shlobodon]

i may be able to a bit later.

do you know anything that could make Excel.CurrentWorkbook(){[Name="Location2"]}[Content] recognizable? like VALUE(Excel.CurrentWorkbook(){[Name="Location2"]}[Content]).

 

[sandy666]

i may be able to a bit later.

so i'll be back tomorrow

 

[shlobodon]

i got it. format that worked was Excel.CurrentWorkbook(){[Name="Location2"]}[Content]{0}[Column1] = null how could i have forgotten [0][Column1]!!!!???!?!? so silly of me

feel like a toddler with one of those wooden puzzles that are only like 3 large pieces

 

[sandy666]

nice,
have a good day