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

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

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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