Joining columns into Fewer columns and multiple rows

[hmil]

Is it possible to manipulate a table in Power BI from this:

DATE  LOCA-QUALA    LOCA-QUALB   LOCA-QUALC   LOCB-QUALA   LOCB-QUALB   LOCB-QUALC

1/01   0.1                     0.2                     0.3                    0.4                    0.5                    0.6

1/02   0.7                     0.8                     0.9                    1.0                    1.1                    1.2

Desired:

DATE Location QualityA  QualityB  QualityC

1/01   A            0.1           0.2           0.3

1/01   B            0.4           0.5           0.6 

1/02   A            0.7           0.8           0.9

1/02   B            1.0           1.1           1.2

I need to be able to isolate the Location Value from the Columns to its own column and I am unsure on how to do so.

Thank you!

[Ruksuro]

If this data is sourced from TSQL (or some other manipulatable source) I would be inclined to select each column individually and perform a union of each select:

Select

   Date,

   'A' as Location,

   LOCA-QUALA as QualityA, 

   LOCA-QUALB as QualityB,   

   LOCA-QUALC as QualityC

from [Table]

union all

Select

   Date,

   'B' as Location,

   LOCB-QUALA as QualityA, 

   LOCB-QUALB as QualityB,   

   LOCB-QUALC as QualityC

from [Table]

However I might take this a step further:

Select

   Date,

   'A' as Location,

   'A' as Quality,

   LOCA-QUALA as [Metric], 

from [Table]

union all

Select

   Date,

   'A' as Location,

   'B' as Quality,

   LOCA-QUALB as [Metric], 

from [Table]

union all

[etc]

If you can't manipulate in this way and it has to be PowerBI let me know and I'll give it a go,

This is the trouble with storing dimension information in the field name...

[Ruksuro · Accepted solution]

Ok, had a look, you want this option below:

Highlight all columns except the data and pivot, you'll then have to extract the info from the "Attribute" column name strings.

Give me a shout if you don't know how to do this.

[hmil]

Got it Thank you!