Dataverse

[JeezyB0Y]

Hi ,

I have Dataverse table which contains whole data. Now I want to create a view which can display only unique rows(as in combination of few columns and the corresponding data) of that table and also add calculated columns. But while creating views filtering option is showing only one custom column and not other columns( most of the columns are of single line text type).. Please Suggest the possible solution if it is not possible with views then whichever way it’s possible….open for power automate or any other solution in power platform .

[citdevpros · Accepted solution]

This sounds like the perfect job for a Dataflow. Not sure how familiar you are so here's the starter link:

https://learn.microsoft.com/en-us/power-query/dataflows/create-use

Keep in mind the Dataflow doesn't surface the data for you - it will read your table, transform your data, and then let you put it somewhere else. You then still must read this destination table. You can however build Dataflows directly in to Power BI if that is your reporting tool.

Dataflows are ideal for reading "whole" data and breaking it up into it's component parts i.e. you can break up the data into all of the different dimensions or slices e.g. get all of the listed statuses and put them into a status table, get all the listed users and put them into a user table etc but it all depends on your appetite and how excited you get when modelling data. Of course, if you just want to Filter and Distinct then it will do that too 😊.

If you are using Dataverse for Teams, Dataflows are available right in the Power Apps editor:

If you are using Dataverse, Dataflows are accessible from the main nav bar on the left. (If not there, choose the More option and add it):

[JeezyB0Y]

Thank you. Looks like it might work for me 

[JeezyB0Y]

I tried it but I have a column where it does a combination of Arrayformula and sumif (of excel) . I tried to create one custom column with similar formula but it is throwing error as cyclic reference error..

= List.Sum(
   Table.SelectRows(
       #"table1",
       each [H] = B2 and [L] = E2 and [B] = A2 and [F] = C2 and [G] = D2 and [M] = F2
   )[K]
)

[JeezyB0Y · Accepted solution]

Issue sorted using Dataflow. Used combination of Table.Distinct and List.sum in filtered rows @citdevpros  Thank you 

[citdevpros]

@JeezyB0Y that's great to hear. Well done on the solution.