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

Power Apps transforming rows to collumns

Hey everybody, 

 

I am currently building an app which should show how a store is performing on its KPIS in a given calendar week.

 

I have a table "Mains" in the Dataverse which is scructured like this: 

 

YearCalender WeekStore IDKPIKPI Value
202047101Sales20
202046101Sales24
202047902Sales16
202046902Sales19
202047101Return2
202046101Return1
202047902Return4
202046902Return2

 

I wanted to use a gallery to visualise the data like this: 

 

Gallery for 2020 in Calender Week  47:

 

Store IDSalesReturn
101202
902164

 

So far I am trying to do this in the frontend of the app.

 

First get a collection with group by (there is are alos quite a few other colmuns in the Main table but not needed) 

 

 

 

ClearCollect(
    KPI_Store_View,GroupBy(Filter(Mains,CW=47),"cr02d_storeid","cr02d_cw","cr02d_year", "ID")
)

 

 

 

Then I want to work with the collection, which uses the ID in a look up to find the correct value on the column "KPI_Value":

 

 

AddColumns(
    KPI_Depot_View,
    "Sales",LookUp(Filter(Mains,CW=47), 'Depot ID' = cr02d_depotid && 'KPI Name' ="Sales").'KPI Value',
    "Return",LookUp(Filter(Mains,CW=47),'Depot ID'=cr02d_depotid && 'KPI Name' ="Return",'KPI Value')
)   

 

 

 

This transforms the KPI names into the columns but unfortuntely the result is not as expected: 

 

Store IDSalesReturn
101202
902202

 The code pulls the store ID as expected out of KPI_Depot_View. The Lookup only works once and copies the result form his first hit to all upcoming rowes. 

 

If tried a million other ways to transofrom the data, but this is the closest I got. Do you have any suggestions? 

 

Unfortuntaly I am not too familiar with the datavers yet. Is there a way to do the transformation in the backend? 

 

Greetings and have a great weekend 

 

Lasse 

1 ACCEPTED SOLUTION

Accepted Solutions
rubin_boer
Super User
Super User

Hi @Piggi 

 

you can get away by grouping them. 

rubin_boer_0-1612550688945.png

The filter for the weeknumber is hardcoded but you can filter it with another control.

this group the data and add two columns, Sales and Return. 

ClearCollect( colGrouped,
AddColumns(GroupBy(Filter(colSomeData,CalenderWeek = 47),"StoreID","Store"),

"Sales", Sum(Filter(Store,KPI = "Sales"),KPIValue),
"Returns", Sum(Filter(Store, KPI = "Return"),KPIValue)))

 

Give it a try.

 
hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.

View solution in original post

6 REPLIES 6
rubin_boer
Super User
Super User

Hi @Piggi 

 

you can get away by grouping them. 

rubin_boer_0-1612550688945.png

The filter for the weeknumber is hardcoded but you can filter it with another control.

this group the data and add two columns, Sales and Return. 

ClearCollect( colGrouped,
AddColumns(GroupBy(Filter(colSomeData,CalenderWeek = 47),"StoreID","Store"),

"Sales", Sum(Filter(Store,KPI = "Sales"),KPIValue),
"Returns", Sum(Filter(Store, KPI = "Return"),KPIValue)))

 

Give it a try.

 
hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.
Drrickryp
Super User
Super User

Hi @Piggi 

Would this work for you? No collections needed.

_1.jpg

Good Morning, 

 

unfortunately that is not possible as the data needs to be comparable and this underneath each other. 

 

Thanks though !

hi @Piggi the StoreId Header in the second gallery should be Return, sorry for that

 
hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.

@rubin_boer  Thanks a lot. That seems to do the job 🙂 

Do you have an explanation why my solution did not work? 

 

Greetings

 

Lasse

rubin_boer
Super User
Super User

hi there @Piggi 

 

the lookup returns the first value which matches your criteria, which is the value 20 for both id's, as the depot id do not have an evaluation, it is the first id matched being returned. the same reason for the Return. 

 

I hope that helps.

 
hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (6,110)