Power Query If Statement

T

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
5,274
Office Version
  1. 365
Platform
  1. Windows
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:

Power Query: 
 #"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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
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.

Power Query: 
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
 
Upvote 0
Hi smozgur,

Thanks for the reply.

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

Power Query: 
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
 
Upvote 0
You are missing the closing parenthesis in the Source step.
Power Query: 
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:
Power Query: 
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
 
Upvote 1
Actually this works...

Power Query: 
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
 
Upvote 0
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 :mad:
 
Upvote 0
Looks like null works:

Power Query: 
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])
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
Power Query to Retrieve Data from a Specific Sheet in an External Workbook
Replies
7
Views
2K
Trebor76
T
T
  • Question
Help converting Power Query that imports data to VBA
Replies
0
Views
379
The Shy Retirer
T
Q
  • Solved
Defined Name use in Power Query
Replies
1
Views
246
RoryA
RoryA
T
  • Solved
Power Query to use a cell reference as a filter
Replies
5
Views
389
Trebor76
T
Q
  • Solved
Loading Mulitple CSV filenames
Replies
2
Views
571
qlander
Q

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top