Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi! I have a table that has thousands of rows of models with a separate row for each PN that the model has associated with it.
Brief Example (real data has 200k rows):
Model1 Device2 PNA
Model1 Device2 PNB
Model2 Device3 PNA
What I want to do is create a new table that looks like this. Columns should be each possible PN, rows should be Models.
Model1 PNA PNB
Model2 PNA
The ultimate goal is to determine which models have a specific combo of PNs that will determine their pricing.
Solved! Go to Solution.
You can achieve this by pivoting the PN values into columns for each Model using Power Query in Power BI. Here's how:
Load Your Data into Power BI
Open Power Query Editor
Add a Helper Column
1
.Pivot the PN Column
Clean Up the Resulting Table
null
with 0
or leave it blank.Finalize and Apply
Result:
You will have a new table where each Model is a row, and each PN is a column indicating its presence:
Model | PNA | PNB |
---|---|---|
Model1 | 1 | 1 |
Model2 | 1 | 0 |
Now you can:
Note: This method efficiently handles large datasets and simplifies analysis of model-PN relationships.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Hi @SherriBelizeNV,
You may also want to check the following approach. Just paste the code below into Advanced editor in Power Query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s1PSc0xVNJRckkty0xONQKyAvwclWJ1cEg5IaSM4FLG6LqM4VImuKUwDERIGYKlnJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Model = _t, Device = _t, PN = _t]),
colNames = List.Distinct(Source[PN]),
#"Grouped Rows" = Table.Group(Source, {"Model"}, {{"PNs", each Record.FromList(_[PN], _[PN]) }}),
#"Expanded PNs" = Table.ExpandRecordColumn(#"Grouped Rows", "PNs", colNames)
in
#"Expanded PNs"
Output:
Hope it helps!
Hi @SherriBelizeNV ,
Did @wini_R reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.
Best regards,
Adamk Kong
Hi @SherriBelizeNV,
You may also want to check the following approach. Just paste the code below into Advanced editor in Power Query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s1PSc0xVNJRckkty0xONQKyAvwclWJ1cEg5IaSM4FLG6LqM4VImuKUwDERIGYKlnJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Model = _t, Device = _t, PN = _t]),
colNames = List.Distinct(Source[PN]),
#"Grouped Rows" = Table.Group(Source, {"Model"}, {{"PNs", each Record.FromList(_[PN], _[PN]) }}),
#"Expanded PNs" = Table.ExpandRecordColumn(#"Grouped Rows", "PNs", colNames)
in
#"Expanded PNs"
Output:
Hope it helps!
You can achieve this by pivoting the PN values into columns for each Model using Power Query in Power BI. Here's how:
Load Your Data into Power BI
Open Power Query Editor
Add a Helper Column
1
.Pivot the PN Column
Clean Up the Resulting Table
null
with 0
or leave it blank.Finalize and Apply
Result:
You will have a new table where each Model is a row, and each PN is a column indicating its presence:
Model | PNA | PNB |
---|---|---|
Model1 | 1 | 1 |
Model2 | 1 | 0 |
Now you can:
Note: This method efficiently handles large datasets and simplifies analysis of model-PN relationships.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
This is great! It does exactly what I want. I am however getting 'mismatch' errors in some of the columns. Any idea what is triggering those? I think they should be a value of 1 but am not sure. Thx!
Nevermind! I figured it out. I needed to have distinct values. Thank you!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
57 | |
55 | |
55 | |
37 | |
30 |
User | Count |
---|---|
78 | |
64 | |
45 | |
43 | |
40 |