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

AddColumns doesn't return all data

I am having an issue with adding a calculated column using Azure SQL as the back end. I tried using a collection but kept getting inconsistent results. 

 

If I use: 
ShowColumns('[dbo].[ComplianceItems]',"app_id","completed_date","compliance_item","complianceitem_id","duedate","item_completed")

All Fileds Return.

 

However, if I use:
AddColumns(ShowColumns('[dbo].[ComplianceItems]',"app_id","completed_date","compliance_item","complianceitem_id","duedate","item_completed"),"DaysDue", DateDiff(Now(),duedate))

 

it doesn't return app_id.  If I do this in a collection using ClearCollect, it will return the app_id once, and then not again.

 

I can't figure out why it's dropping app_id. 

 
1 ACCEPTED SOLUTION

Accepted Solutions

I found my issue. I had "Explicit column selection" under App Settings>Advanced Settings>Preview Features turned on. Once I turned it off everything worked as expected...

View solution in original post

4 REPLIES 4
PowerAddict
Super User
Super User

Hi, 

 

Have you tried this in the reverse order? 

ShowColumns(AddColumns('[dbo].[ComplianceItems]', "DaysDue", DateDiff(Now(),duedate)),"app_id","completed_date","compliance_item","complianceitem_id","duedate","item_completed","DaysDue")

 

Let me know if this works. 

 

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution.

Thanks!
Hardit Bhatia
https://thepoweraddict.com

It gave the same result. app_id isn't showing. It seems that any time I do a filter or an addcolumns it drops the app_id. 

I'm finding that any time I apply a filter, it only returns the filtered columns. As a test, I created a view in SQL to give me the data I need. If I do the following:
Refresh('[dbo].[v_BusySeasonCalendar]');
ClearCollect(colCompliance_Jan_Temp2,'[dbo].[v_BusySeasonCalendar]');
ClearCollect(colCompliance_Jan_Temp3,(colCompliance_Jan_Temp2)

It returns all rows in from the DB in both collections. If I filter the Temp3, it only shows the DueMonth in both Temp2 and Temp3.

Refresh('[dbo].[v_BusySeasonCalendar]');
ClearCollect(colCompliance_Jan_Temp2,'[dbo].[v_BusySeasonCalendar]');
ClearCollect(colCompliance_Jan_Temp3,Filter(colCompliance_Jan_Temp2,DueMonth=1))

 

Why would it not return all columns for both Collections?

 

I found my issue. I had "Explicit column selection" under App Settings>Advanced Settings>Preview Features turned on. Once I turned it off everything worked as expected...

View solution in original post

Helpful resources

Announcements
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,867)