Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
SherriBelizeNV
New Member

Create a table from existing using column values as new column headers

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.

 

2 ACCEPTED SOLUTIONS
VahidDM
Super User
Super User

Hi @SherriBelizeNV 

 

You can achieve this by pivoting the PN values into columns for each Model using Power Query in Power BI. Here's how:

  1. Load Your Data into Power BI

  2. Open Power Query Editor

    • Go to Home > Transform data.
  3. Add a Helper Column

    • In Power Query Editor, go to Add Column > Custom Column.
    • Name the column Value and set the formula to 1.
    • This column will indicate the presence of each PN.
  4. Pivot the PN Column

    • Select the PN column.
    • Go to Transform > Pivot Column.
    • In the Pivot Column dialog:
      • Values Column: Select the Value column you just created.
      • Advanced Options: For Aggregate Value Function, choose Don't Aggregate.
  5. Clean Up the Resulting Table

    • Replace nulls with blanks or zeros:
      • Select all PN columns.
      • Go to Transform > Replace Values.
      • Replace null with 0 or leave it blank.
  6. Finalize and Apply

    • Click Home > Close & Apply to load the transformed data into Power BI.

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:

  • Identify models with specific combinations of PNs.
  • Use this table to determine pricing based on PN combinations.

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

 

LinkedIn|Twitter|Blog |YouTube 

View solution in original post

wini_R
Solution Supplier
Solution Supplier

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:

wini_R_0-1732222191721.png

 

Hope it helps!

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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

wini_R
Solution Supplier
Solution Supplier

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:

wini_R_0-1732222191721.png

 

Hope it helps!

VahidDM
Super User
Super User

Hi @SherriBelizeNV 

 

You can achieve this by pivoting the PN values into columns for each Model using Power Query in Power BI. Here's how:

  1. Load Your Data into Power BI

  2. Open Power Query Editor

    • Go to Home > Transform data.
  3. Add a Helper Column

    • In Power Query Editor, go to Add Column > Custom Column.
    • Name the column Value and set the formula to 1.
    • This column will indicate the presence of each PN.
  4. Pivot the PN Column

    • Select the PN column.
    • Go to Transform > Pivot Column.
    • In the Pivot Column dialog:
      • Values Column: Select the Value column you just created.
      • Advanced Options: For Aggregate Value Function, choose Don't Aggregate.
  5. Clean Up the Resulting Table

    • Replace nulls with blanks or zeros:
      • Select all PN columns.
      • Go to Transform > Replace Values.
      • Replace null with 0 or leave it blank.
  6. Finalize and Apply

    • Click Home > Close & Apply to load the transformed data into Power BI.

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:

  • Identify models with specific combinations of PNs.
  • Use this table to determine pricing based on PN combinations.

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

 

LinkedIn|Twitter|Blog |YouTube 

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!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.