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

Joining columns into Fewer columns and multiple rows

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Ruksuro Member
Member

Re: Joining columns into Fewer columns and multiple rows

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

 

Capture.JPG

 

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.

 

 

3 REPLIES 3
Ruksuro Member
Member

Re: Joining columns into Fewer columns and multiple rows

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

 

Highlighted
Ruksuro Member
Member

Re: Joining columns into Fewer columns and multiple rows

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

 

Capture.JPG

 

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 Frequent Visitor
Frequent Visitor

Re: Joining columns into Fewer columns and multiple rows

Got it Thank you!