cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
90_percent
Helper I
Helper I

Building a collection from a dataverse Filtered table.

i am attempting to  create a collection of filtered items from a dataverse table.

DataVerse table: 4-IEX_Data

Dataverse Table View: Data_Filtered_IEX_Data

Collection: Filtered data

 

the command I am using is 

 

ClearCollect(
       FilteredData, '4-IEX_Datas (Views)'.Data_Filtered_IEX_Data)

 

The output i get is this.

90_percent_0-1644644039156.png

If i use this command

 

ClearCollect(
FilteredData, '4-IEX_Datas')

 

 

I get this:

90_percent_1-1644644266221.png

the problem is i am wanting to filter the data,(this is a large table, and is growing each day. i have tried this way, by filtering thee table via a view,

 

and i tried filtering the collection by using a  lookup table. trimming it down by todays date,

 

I am stumped, and have spent 12 hours on this...my google foo is weak this week. 

 

any direction on how to do this would be apprecaited.

 

thanks


Guy

 

 

6 REPLIES 6
PowerRanger
Resident Rockstar
Resident Rockstar

@90_percent have a Look at the ShowColumns() function

 

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.




​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.
sheppegr
Frequent Visitor

@PowerRanger  Thanks for the suggestion. This will be an answer to the question I have after I figure this out. How to only see the columns I need, versus all 60 columns. 
I will clarify a little more on my issue. It has to do with delegation.
I am wanting to create the collection, based on a lookup. If I use the Lookup field on the column Import_Date, and match it to today (if Present) or Yesterday (if today isn't present), this will bring the returned fields down to roughly 150-200 items, for 4 of the 5 pages.
The 5th page, it will bring it down from 50K down to 9K, that will be a different issue, 😛

Here is what i was thinking this morning. 

ClearCollect(NewFilteredDate, 
LookUp(
'4-IEX_Datas',
 Import_Date = Today()
)

but the error on this formula is that NewFilteredDate (name isn't valid. This identifier isn't recognized.)
Any Suggestions are welcomed and appreciated.

 

_Guy

 



90_percent
Helper I
Helper I

Ok, so I have come up with this formula. 

ClearCollect(NewDataFiltered, '4-IEX_Datas');

Set(varDate,{
    Data_Date:  If(Value(LookUp(NewDataFiltered, Ticker = "AA").Import_Date) = Today (), {Date: Today()}, {Date: DateAdd(Today(),-1,Days)})
     });

ClearCollect(TodaysFilteredData, (Filter(NewDataFiltered, Import_Date = varDate.Data_Date.Date)))

 

thought process, 
1. get all the data that is in the table,

2. look for the latest date for a single record ("AA") and use it to set a variable,

3. use that variable to filter the new Collection of the data into another smaller collection. 

        a. this should give me the data for the most recent date. (either today or Yesterday) 

 

Its a good thought process, but unfortunately it doesn't work. 

 

Any suggestions?

 

_Guy

 

Ok, Just updating this for those of you who are following along.

Set(varDate,(If(TimeValue(Text(Now())) >= TimeValue("5:00:00 PM"), Today(), DateAdd(Today(),-1,Days))));

ClearCollect(TodaysFilteredData, Filter('2-Finviz_Stockss', Import_Date = varDate))

This is the formula i am using now, this gets me the date set correctly, and changes at 5pm each day.
the ClearCollect filters down the table to the import Date matching the varDate variable. 

Now the issue is that it only shows 2 columns when I look at the collection. (primary_Key and Import_date)

(credit where credit is due, I got help from people over on the discord channel (PowPco) great resource as well)

90_percent
Helper I
Helper I

Tonights update - there is a toggle for the issue with the columns-
> Settings > Upcoming features > Preview, and then turn off the Explicit column selection toggle.

now all the columns show up, i will turn it off it causes loading issues.

SO we are getting closer to a full solution

 

-Guy

sheppegr
Frequent Visitor

ok. so, I know have the smaller collections working correctly and posting in galleries.

Now i am off to start another thread on how to get past delegation in a large table in the Dataverse!

Thank you all for your help. and hope this helps someone in the future. 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (2,630)