Unpivot last 2 columns

[Mazilinho]

Hello, I have the following scenario: 

Category	Brand	KPI	Values
Brand	Sony	A	123;322;221
Brand	Sony	B	234;221;232

I need the data to be displayed like this:

Category	Brand	KPI A Value	KPI B Value
Brand	Sony	123;322;221	234;221;232

How can I obtain this? I tried to use IF clause to calculate 2 new columns and then use only() in the load script to obtain that format, but it results no rows when I use only().

Thanks!

[Or]

Load Category, Brand, sum(if(KPI = 'A',Values)) as [KPI A Value], sum(if(KPI = 'B',Values)) as [KPI B Value]

From YourTable

Group by Category, Brand;

Sample:

YourTable:
Load * INLINE [
Category, Brand, KPI, Values
Brand, Sony, A, 123
Brand, Sony, B, 234 ];

Load Category, Brand, sum(if(KPI = 'A',Values)) as [KPI A Value], sum(if(KPI = 'B',Values)) as [KPI B Value]
Resident YourTable
Group by Category, Brand;

Drop Table YourTable;

[Mazilinho]

The format of Values column is actually an array: 123;452;234

[Or]

Didn't catch that. Just replace the sum() with only(), then.

Load Category, Brand, Only(if(KPI = 'A',Values)) as [KPI A Value], Only(if(KPI = 'B',Values)) as [KPI B Value]

[Mazilinho]

Thanks, yes, I fixed it with only() in the end. Not sure what I was doing wrong, but the app was crashing when I used only(). Thanks for help!