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.

 

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.

 

View solution in original post

Drrickryp
Super User
Super User

Hi @Piggi 

Would this work for you? No collections needed.

_1.jpg

Piggi
Frequent Visitor

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

 
Piggi
Frequent Visitor

@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.

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,030)