cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BenMunk
Frequent Visitor

If/Switch data source not returning all columns (sharepoint list)

It seems if I use IF to change data sources of  filter(), most of the columns of the sharepoint list are not returning.

 

I have tried with both IF and Switch.  The formula appears to work, but the result returned to the Collection omits a number of columns from the list.

 

ClearCollect(blah,
    Filter(
        If(
            A = "foo", 
                fooList,
            A = "bar", 
                barList

        ), 
        SRID = currentReport.ID
    )
);

 

If I remove the 2nd condition it works as expected, returning all columns from the list into the Collection.

 

ClearCollect(blah,
    Filter(
        If(
            A = "foo", 
                fooList
        ), 
        SRID = currentReport.ID
    )
);

 

I have also tried wrapping both If and Switch on the outside and repeating the Filter function for each case, but that doesn't seem to be working either. 

 

I found some posts which suggest there was a bug in 2018.  But nothing helpful since then.

 

Any ideas, or workarounds?

1 ACCEPTED SOLUTION

Accepted Solutions

@BenMunk ,

And there is the problem (I should have seen it before) - both collection options need to have the same fields and field types, or you could refer to it later on with an invalid query - Power Apps enforces this.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

 

View solution in original post

4 REPLIES 4
WarrenBelz
Super User III
Super User III

Hi @BenMunk ,

Might be painful and a step backwards, but this should work

If(
   A = "foo",
   ClearCollect(
      blah,
      Filter(
         fooList,
         SRID = currentReport.ID
      )
   ),  
   A = "bar", 
   ClearCollect(
      blah,
      Filter( 
         barList,
         SRID = currentReport.ID
      )   
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Many thanks for your reply.

I tried this, but the formula is getting an error "Incompatible Type: the item has a type not compatible with the Collection.

 

Seems it doesn't like to conditionally create Collection "blah". 

 

With the same formula, I can conditionally create Collection blah_a  and blah_b just fine.

 

Maybe a bug??

@BenMunk ,

And there is the problem (I should have seen it before) - both collection options need to have the same fields and field types, or you could refer to it later on with an invalid query - Power Apps enforces this.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

 

View solution in original post

Ah huh!  (light bulb goes on)

 

That's why it was returning some fields and not others - seems it may have been returning the fields that were common across all conditional data sources.

 

I guess I will need to rethink my approach...

 

Thanks!

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (3,424)