Forum Discussion
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!
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.
3 Replies
RuksuroHelper III
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...
