cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Power Apps can't convert this Text to Table

Please take it easy on me. I just started with Programming in Power Apps. I'm currently getting "Can't convert this data type. Power Apps can't convert this Text to Table"

I have two SharePoint list in my app. 'PPA Metric Admins' and 'MRM Notes Master'.  'MRM Notes Master'.MetricName is a single text column while 'PPA Metric Admins'.Metrics is a multiple text column. I'm trying to say when user().Email is true or found, filter 'MRM Notes Master' to just show the metrics that match in 'PPA Metrics Admins'.Metrics. On line 11 I'm getting that error message in the subject. 

What should I do here?

How can I get better and programing this language?

If(
    LookUp(Filter('PPA Metric Admins', Title = "_Admin"), User().Email in 'UserName/Email'.Email, true),
    Filter(
        'MRM Notes Master',
        Active.Value = "Yes")
    ,
If(    
    LookUp('PPA Metric Admins', User().Email in 'UserName/Email'.Email, true),
    Filter('MRM Notes Master',
        Active.Value = "Yes",
        MetricName in ForAll('PPA Metric Admins', Metrics)
    )
    )

 

9 REPLIES 9
Highlighted
Super User III
Super User III

@adjohnson2 

It's hard to tell exactly what you have as I don't know what the column types are that you are using.  So I will assume for the most part that they are text.

Your formula can be set as follows:

With({isAdmin: !IsBlank(LookUp('PPA Metric Admins', 'UserName/Email'.Email = User().Email, ID))},

    Filter('MRM Notes Master',
        Active.Value = "Yes" &&
        (isAdmin && (MetricName in ForAll('PPA Metric Admins', Metrics)))
    )
)

I am not clear on your logic for the Active value against the lookup, but I believe the above formula is what you are trying to say.

If there are continued errors on it, then let me know what column types you are referring to in your formula.

 

Keep in mind as you learn...you're not learning to program as PowerApps is not a programming platform.  You are creating formulas exactly like you would do in Excel.  The more you think in those terms as opposed to programming, the easier it will be.

 

I hope this is helpful for you.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!
Highlighted

I had to adjust your code because I was getting a error message at line 1. I’m still getting the same error message at line 1.

I hope I’m understanding you on the column types used, see list below. They all have string values.
‘Active’ is of type Choice.
‘UserName/Email’ is of type Person or Group

‘MetricName’ is of type Single lines of text.
‘Metrics’ is of type Choice.

With({isAdmin: !IsBlank(LookUp('PPA Metric Admins', User().Email in 'UserName/Email'.Email, ID))},



    Filter('MRM Notes Master',

        Active.Value = "Yes" &&

        (isAdmin && (MetricName in ForAll('PPA Metric Admins', Metrics)))

    )

)
Highlighted

@adjohnson2 

So the Formula looks good.  I am assuming if you had to adjust the Formula as you mention to be in rather than =, that your Person column is defined to allow multiple people.

 

Now...the $10,000 question...where are you putting this Formula?  What Control type and what Property?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!
Highlighted

Yes to your assumption and I swapped the comparisons. As for the $10k question, I'm putting the results in a browseGallery.

Highlighted

@adjohnson2 

Well, the formula looks good.  Tell me this - you are seeing an error in the formula and most likely you have red underlines on the formula.  Can you hover over the underlined area and see what pops up in the tooltip for the error, and specifically, where in the formula you see the error. 

A screen shot might help to.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!
Highlighted

I've attached an image of the error message. 

 

Capture.PNG

Highlighted

@adjohnson2 

AH...I should have caught that after your definition of the column types!

Change formula to the following:

With({isAdmin: !IsBlank(LookUp('PPA Metric Admins', User().Email in 'UserName/Email'.Email, ID))},
    Filter('MRM Notes Master',
        Active.Value = "Yes" &&
        (isAdmin && (MetricName in ForAll('PPA Metric Admins', Metrics.Value)))
    )
)

 

That should clear the error...next we might need to work on the logic for display what you want properly.  I still am unclear if I matched your original requirement for what should and shouldn't be displayed.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!
Highlighted

I'm still getting the same error message at the same location.Capture.PNG

Highlighted

@adjohnson2 

Okay...if you are getting that error there now, then you neglected to convey one little bit of information - your Metrics choice column is defined as a Multiple Choice column (checkboxes) in SharePoint.

So, I am a little confused over your formula in that you are trying to compare to all metrics values in all of the rows of the PPA Metrics Admins columns - not sure if that is what you really want, but let's proceed...

This formula would resolve the multi-choice columns:

With({isAdmin: !IsBlank(LookUp('PPA Metric Admins', User().Email in 'UserName/Email'.Email, ID))},
    Filter('MRM Notes Master',
        Active.Value = "Yes" &&
        (isAdmin && 
           (MetricName in 
              Concat('PPA Metric Admins', 
                  If(CountRows(Metrics)>0, 
                     Concat(Metrics, Value & "|")
                  ), 
                  ""
              )
            )
         )
    )
)

 

This will concat all of the values in all of the Metrics column in all of the rows of the datasource.  Finally, in the context of the Filter, would compare to see if MetricName is in that resultant string of values.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (10,574)