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

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:

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 

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!