Power Apps transforming rows to collumns

[Piggi]

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: 

Year	Calender Week	Store ID	KPI	KPI Value
2020	47	101	Sales	20
2020	46	101	Sales	24
2020	47	902	Sales	16
2020	46	902	Sales	19
2020	47	101	Return	2
2020	46	101	Return	1
2020	47	902	Return	4
2020	46	902	Return	2

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

Gallery for 2020 in Calender Week  47:

Store ID	Sales	Return
101	20	2
902	16	4

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 ID	Sales	Return
101	20	2
902	20	2

 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 

[rubin_boer · Accepted solution]

Hi @Piggi 

you can get away by grouping them. 

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.

[Drrickryp]

Hi @Piggi 

Would this work for you? No collections needed.

[Piggi]

Good Morning, 

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

Thanks though !

[rubin_boer]

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

[Piggi]

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

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.