Power Query If Statement

[Trebor76]

Hi there,

I have a named range for a single cell called SrcSheet. If this is not populated I want to import the first (furthest left) tab of a workbook via Power Query or else import the sheet name that's been entered. I have this but it keeps erroring out:

 #"Promoted Headers" = Table.PromoteHeaders(if(Text.Length(Excel.CurrentWorkbook(){[Name="SrcSheet"]}[Content]{0}[Column1]))=0 then Source{0}[Data] else Text.From.Excel.CurrentWorkbook(){[Name="SrcSheet"]}[Content]{0}[Column1]))

If anyone could please provide me the correct syntax it would be greatly appreciated.

Regards,

Robert

 

[smozgur]

Both sources return primitive data types. SrcSheet returns a cell value, and Source{0}[Data] returns the value in the "Data" column of the first row in the Source table, which is also a primitive value. So, you don't have a table of data to use in the Table.PromoteHeaders() function. This is the first issue in that line of code since this function is not necessary (can't be used) in that code line.

The second problem is the method you're employing to obtain the result. Instead of trying to retrieve the result in a single step, I suggest to break down the problem into multiple steps using extra identifiers. Consider the following code as an example. In the code, I first obtain the SrcSheetValue in the initial step and then use this identifier in the next step. This approach not only improves the code's readability but also prevents duplicate use of the same functions.
Note: The first line, Source, is the sample data I used. You should replace it with your own Source identifier.

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMTS1WCM7NLMlQitWJVnLOKMosLskvyEgtUnDMS0ktKs7Pg0joKfhmJmek5uSkKnjlZ+SBxWMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    SrcSheetValue = Text.From(Excel.CurrentWorkbook(){[Name="SrcSheet"]}[Content]{0}[Column1]),
    ExpectedValue = if Text.Length(SrcSheetValue) = 0 then Source{0}[Data] else SrcSheetValue
in
    ExpectedValue

 

[Trebor76]

Hi smozgur,

Thanks for the reply.

I have this but it generates a "Token Literal expected" error:

let
    Source = Excel.Workbook(File.Contents("C:\Users\MyFile.xlsx"), 
    SrcSheetValue = Text.From(Excel.CurrentWorkbook(){[Name="SrcSheet"]}[Content]{0}[Column1]),
    ExpectedValue = if Text.Length(SrcSheetValue) = 0 then Source{0}[Data] else SrcSheetValue
in
    ExpectedValue

I'd appreciate a little more help to get the syntax correct if you can.

Many thanks,

Robert

 

[smozgur]

You are missing the closing parenthesis in the Source step.

Source = Excel.Workbook(File.Contents("C:\Users\MyFile.xlsx")),

By the way, now looking at your Source step, It is actually returning a table since you are importing from an Excel workbook. However, you still cannot use PromoteHeaders function because if the SrcSheet value is returned from the condition then the query will fail. Is it intentional that your condition line, ExpectedValue step, returns either a primitive value (text, numeric, date, etc) or a table?

In that case, you can use the PromoteHeaders function as shown below:

let
    Source = Excel.Workbook(File.Contents("C:\Users\MyFile.xlsx")), 
    SrcSheetValue = Text.From(Excel.CurrentWorkbook(){[Name="SrcSheet"]}[Content]{0}[Column1]),
    ExpectedValue = if Text.Length(SrcSheetValue) = 0 
                        then Table.PromoteHeaders(Source{0}[Data], [PromoteAllScalars = true]) 
                        else SrcSheetValue
in
    ExpectedValue

 

[Trebor76]

Actually this works...

let
  strFullPath = Excel.CurrentWorkbook(){[Name="SrcFile"]}[Content]{0}[Column1],
  Source = Excel.Workbook(File.Contents(strFullPath)),
  SrcSheetValue = Excel.CurrentWorkbook(){[Name="SrcSheet"]}[Content]{0}[Column1],
  ExpectedValue = if Text.Length(SrcSheetValue) = 0 then Source{0}[Data] else  Source{[Item=SrcSheetValue, Kind="Sheet"]}[Data]
in
    ExpectedValue

...thank you.

On a side note there are a lot of ghost (blank) rows and columns after importing. Is there any way to stop these?

Kind regards,

Robert

 

[Trebor76]

Ah - if I put Sheet1 as a parameter it doesn't find the sheet but it definitely exists. I think I might just use VBA

 

[Trebor76]

Looks like null works:

SrcSheetValue = Excel.CurrentWorkbook(){[Name="SrcSheet"]}[Content]{0}[Column1],
  ExpectedValue = if SrcSheetValue is null then Table.PromoteHeaders(Source{0}[Data],[PromoteAllScalars = true]) else Table.PromoteHeaders(Source{[Item=SrcSheetValue, Kind="Sheet"]}[Data],[PromoteAllScalars = true])