Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I have a table like below. I want to unpivot columns below
GroupA_Device_Name
GroupB_Device_Name
GroupA_Device_Category
GroupB_Device_Category
GroupA_Device_Number
GroupB_Device_Number
GroupA_Device_ID
GroupB_Device_ID
Region | SubRegion | Location | Key | Node | Sequence_Category | Score | Items_Type | GroupA_Device_Name | GroupB_Device_Name | GroupA_Device_Category | GroupB_Device_Category | GroupA_Device_Number | GroupB_Device_Number | GroupA_Device_ID | GroupB_Device_ID |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.12.1.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | ESDV-20000 | Basic Process Control System (BPCS) | LALL-20001 | LALL-20001 | |||||
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.2.1.1 | 1. Glycol Contactor / Absorber | P H&S | 3 | PSV-102A/B | Basic Process Control System (BPCS) | PAHH-100/101 | PAHH-100/101 | |||||
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.2.1.1 | 1. Glycol Contactor / Absorber | P H&S | 3 | PSV-102A/B | Basic Process Control System (BPCS) | PAHH-100/101 | PAHH-100/101 | |||||
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.1.2.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | PSV-102A/B | Basic Process Control System (BPCS) | PAHH-100/101 | PAHH-100/101 | |||||
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.1.2.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | PSV-102A/B | Basic Process Control System (BPCS) | PAHH-100/101 | PAHH-100/101 | |||||
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.1.3.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | PSV-102A/B | Basic Process Control System (BPCS) | PAHH-100/101 | PAHH-100/101 | |||||
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.1.3.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | PSV-102A/B | Basic Process Control System (BPCS) | PAHH-100/101 | PAHH-100/101 | |||||
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.1.4.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | PSV-102A/B | Basic Process Control System (BPCS) | PAHH-100/101 | PAHH-100/101 | |||||
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.1.4.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | PSV-102A/B | Basic Process Control System (BPCS) | PAHH-100/101 | PAHH-100/101 | |||||
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.1.5.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | PSV-102A/B | Basic Process Control System (BPCS) | PAHH-100/101 | PAHH-100/101 | |||||
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.1.5.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | PSV-102A/B | Basic Process Control System (BPCS) | PAHH-100/101 | PAHH-100/101 |
I want result like this by unpivoting above columns to below.
Region | SubRegion | Location | Key | Node | Sequence_Category | Score | Items_Type | Device_Name | Value | Device_Category | Value | Device_Number | Value | Device_ID | Value |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.12.1.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | GroupA | xxxx | GroupA | xxxx | GroupA | xxxxx | GroupA | xxxxx | |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.2.1.1 | 1. Glycol Contactor / Absorber | P H&S | 3 | GroupB | xxxx | GroupB | xxxx | GroupB | xxxxx | GroupB | xxxxx | |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.2.1.1 | 1. Glycol Contactor / Absorber | P H&S | 3 | GroupA | xxxx | GroupA | xxxx | GroupA | xxxxx | GroupA | xxxxx | |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.1.2.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | GroupB | xxxx | GroupB | xxxx | GroupB | xxxxx | GroupB | xxxxx | |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.1.2.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | GroupA | xxxx | GroupA | xxxx | GroupA | xxxxx | GroupA | xxxxx | |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.1.3.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | GroupB | xxxx | GroupB | xxxx | GroupB | xxxxx | GroupB | xxxxx | |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.1.3.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | GroupA | xxxx | GroupA | xxxx | GroupA | xxxxx | GroupA | xxxxx | |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.1.4.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | GroupB | xxxx | GroupB | xxxx | GroupB | xxxxx | GroupB | xxxxx |
I want to result like this
Region | SubRegion | Location | Key | Node | Sequence_Category | Score | Items_Type | Device_Name | Value | Device_Category | Value | Device_Number | Value | Device_ID | Value |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.12.1.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | GroupA | xxxx | GroupA | xxxx | GroupA | xxxxx | GroupA | xxxxx | |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.2.1.1 | 1. Glycol Contactor / Absorber | P H&S | 3 | GroupB | xxxx | GroupB | xxxx | GroupB | xxxxx | GroupB | xxxxx | |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.2.1.1 | 1. Glycol Contactor / Absorber | P H&S | 3 | GroupA | xxxx | GroupA | xxxx | GroupA | xxxxx | GroupA | xxxxx | |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.1.2.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | GroupB | xxxx | GroupB | xxxx | GroupB | xxxxx | GroupB | xxxxx | |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.1.2.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | GroupA | xxxx | GroupA | xxxx | GroupA | xxxxx | GroupA | xxxxx | |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.1.3.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | GroupB | xxxx | GroupB | xxxx | GroupB | xxxxx | GroupB | xxxxx | |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.1.3.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | GroupA | xxxx | GroupA | xxxx | GroupA | xxxxx | GroupA | xxxxx | |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.1.4.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | GroupB | xxxx | GroupB | xxxx | GroupB | xxxxx | GroupB | xxxxx |
Result I wanted like below
Region | SubRegion | Location | Key | Node | Sequence_Category | Score | Items_Type | Device_Name | Value | Device_Category | Value | Device_Number | Value | Device_ID | Value |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.12.1.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | GroupA | xxxx | GroupA | xxxx | GroupA | xxxxx | GroupA | xxxxx | |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.2.1.1 | 1. Glycol Contactor / Absorber | P H&S | 3 | GroupB | xxxx | GroupB | xxxx | GroupB | xxxxx | GroupB | xxxxx | |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.2.1.1 | 1. Glycol Contactor / Absorber | P H&S | 3 | GroupA | xxxx | GroupA | xxxx | GroupA | xxxxx | GroupA | xxxxx | |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.1.2.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | GroupB | xxxx | GroupB | xxxx | GroupB | xxxxx | GroupB | xxxxx | |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.1.2.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | GroupA | xxxx | GroupA | xxxx | GroupA | xxxxx | GroupA | xxxxx | |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.1.3.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | GroupB | xxxx | GroupB | xxxx | GroupB | xxxxx | GroupB | xxxxx | |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.1.3.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | GroupA | xxxx | GroupA | xxxx | GroupA | xxxxx | GroupA | xxxxx | |
EURASIA-PACIFIC | ASIA SOUTH | HUBA | 1.1.1.4.1 | 1. Glycol Contactor / Absorber | W H&S | 3 | GroupB | xxxx | GroupB | xxxx | GroupB | xxxxx | GroupB | xxxxx |
Solved! Go to Solution.
Hi @Anonymous ,
Here are the steps you can follow:
1. Transform – UnpivotColumns -- Yellow-marked columns .
2. In Power query. Add Column – Index Column – From 1.
Result:
3. Create calculated table.
Flag1 =
SUMMARIZE('True','True'[Region],'True'[SubRegion],'True'[Location],'True'[Key],'True'[Node],'True'[Sequence_Category],'True'[Score],'True'[Items_Type],'True'[Index])
Table 3 =
VAR _table1 =
FILTER ( 'Flag1', [Group] <> BLANK () )
RETURN
SUMMARIZE (
_table1,
[Region],
[SubRegion],
[Location],
[Key],
[Node],
[Sequence_Category],
[Score],
[Items_Type],
[Group],
"Device_Name", [Group],
"Value1",
MAXX (
FILTER (
ALL ( 'True' ),
OR (
'True'[Attribute] = "GroupA_Device_Name",
'True'[Attribute] = "GroupB_Device_Name"
)
&& LEFT ( 'True'[Attribute], 6 ) = [Group]
&& 'True'[Key] = EARLIER ( 'Flag1'[Key] )
),
[Value]
),
"Device_Category", [Group],
"Value2",
MAXX (
FILTER (
ALL ( 'True' ),
OR (
'True'[Attribute] = "GroupA_Device_Category",
'True'[Attribute] = "GroupA_Device_Category"
)
&& LEFT ( 'True'[Attribute], 6 ) = [Group]
&& 'True'[Key] = EARLIER ( 'Flag1'[Key] )
),
[Value]
),
"Device_Number", [Group],
"Value3",
MAXX (
FILTER (
ALL ( 'True' ),
OR (
'True'[Attribute] = "GroupA_Device_Number",
'True'[Attribute] = "GroupA_Device_Number"
)
&& LEFT ( 'True'[Attribute], 6 ) = [Group]
&& 'True'[Key] = EARLIER ( 'Flag1'[Key] )
),
[Value]
),
"Device_ID", [Group],
"Value4",
MAXX (
FILTER (
ALL ( 'True' ),
OR (
'True'[Attribute] = "GroupA_Device_ID",
'True'[Attribute] = "GroupA_Device_ID"
)
&& LEFT ( 'True'[Attribute], 6 ) = [Group]
&& 'True'[Key] = EARLIER ( 'Flag1'[Key] )
),
[Value]
)
)
4. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @Anonymous ,
Here are the steps you can follow:
1. Transform – UnpivotColumns -- Yellow-marked columns .
2. In Power query. Add Column – Index Column – From 1.
Result:
3. Create calculated table.
Flag1 =
SUMMARIZE('True','True'[Region],'True'[SubRegion],'True'[Location],'True'[Key],'True'[Node],'True'[Sequence_Category],'True'[Score],'True'[Items_Type],'True'[Index])
Table 3 =
VAR _table1 =
FILTER ( 'Flag1', [Group] <> BLANK () )
RETURN
SUMMARIZE (
_table1,
[Region],
[SubRegion],
[Location],
[Key],
[Node],
[Sequence_Category],
[Score],
[Items_Type],
[Group],
"Device_Name", [Group],
"Value1",
MAXX (
FILTER (
ALL ( 'True' ),
OR (
'True'[Attribute] = "GroupA_Device_Name",
'True'[Attribute] = "GroupB_Device_Name"
)
&& LEFT ( 'True'[Attribute], 6 ) = [Group]
&& 'True'[Key] = EARLIER ( 'Flag1'[Key] )
),
[Value]
),
"Device_Category", [Group],
"Value2",
MAXX (
FILTER (
ALL ( 'True' ),
OR (
'True'[Attribute] = "GroupA_Device_Category",
'True'[Attribute] = "GroupA_Device_Category"
)
&& LEFT ( 'True'[Attribute], 6 ) = [Group]
&& 'True'[Key] = EARLIER ( 'Flag1'[Key] )
),
[Value]
),
"Device_Number", [Group],
"Value3",
MAXX (
FILTER (
ALL ( 'True' ),
OR (
'True'[Attribute] = "GroupA_Device_Number",
'True'[Attribute] = "GroupA_Device_Number"
)
&& LEFT ( 'True'[Attribute], 6 ) = [Group]
&& 'True'[Key] = EARLIER ( 'Flag1'[Key] )
),
[Value]
),
"Device_ID", [Group],
"Value4",
MAXX (
FILTER (
ALL ( 'True' ),
OR (
'True'[Attribute] = "GroupA_Device_ID",
'True'[Attribute] = "GroupA_Device_ID"
)
&& LEFT ( 'True'[Attribute], 6 ) = [Group]
&& 'True'[Key] = EARLIER ( 'Flag1'[Key] )
),
[Value]
)
)
4. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
89 | |
87 | |
77 | |
69 | |
68 |
User | Count |
---|---|
220 | |
128 | |
117 | |
82 | |
77 |