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

[SherriBelizeNV]

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.

[VahidDM · Accepted solution]

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 

[Anonymous]

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 · Accepted solution]

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!

[VahidDM · Accepted solution]

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 

[SherriF]

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!

[SherriF]

Nevermind! I figured it out. I needed to have distinct values. Thank you!