Unpivot several columns with attribute and value

[ancorte]

Hello, i have a table with several columns, like this:

So i have in a row a Code Article, and in the same row the code of Material1, the Quantity of Material1, the code of material2, teh Qauntity of material2 and so on.

I want to unpivot, so to have 3 columns:

- the code article

- the code of material

-the code of Quantity

But i'm not to achieve the result....

Anyone could help me ?

[AlienSx]

hello, @ancorte 

let
    Source = your_table,
    rows = Table.ToRows(Source),
    a = 
        Table.FromRows(
            List.Transform(
                rows, 
                (x) => 
                    {x{0}} & 
                    {List.Transform(
                        List.Split(List.Skip(x), 2), 
                        (y) => Record.FromList(y, {"Code", "Qt"})
                    )}
            ), {"ID Articolo", "other"}
        ),
    e_lst = Table.ExpandListColumn(a, "other"),
    e_rec = Table.ExpandRecordColumn(e_lst, "other", {"Code", "Qt"})
in
    e_rec

[v-xinruzhu-msft]

Hi @ancorte 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQxtVDSUTK3NDAHUgZ6lmZAyszEyBJIGeoZgUhTIwMgBWKamxqAhU2VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Code 1" = _t, #"Qt 1" = _t, #"Code 2" = _t, #"Qt 2" = _t, #"Code 3" = _t, #"Qt 3" = _t, #"Code 4" = _t, #"Qt 4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Code 1", Int64.Type}, {"Qt 1", type number}, {"Code 2", Int64.Type}, {"Qt 2", type number}, {"Code 3", Int64.Type}, {"Qt 3", Int64.Type}, {"Code 4", Int64.Type}, {"Qt 4", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Num", each Text.End([Attribute],1)),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Type", each if Text.Contains([Attribute], "Code") then "Code" else if Text.Contains([Attribute], "Qt") then "Qt" else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Attribute"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ID", "Type", "Num", "Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Num", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Type]), "Type", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Num"})
in
    #"Removed Columns1"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dangar332 · Accepted solution]

i take these example

ok problem solve follow below step

1. select  ID Articolo and unpivote other column

2.  replace value  code mat1,  code mat2,  code mat3,  code amt4 to code

    and qt mat1,  qt amt2,  qt amt3,  qt mat4  to qt

3.  add conditional column like these

which look like these  

4. add another conditional column like these

5. copy  qt  column and paste to new table(qt table) and add index column which look like below

6.  now return to original table and add index to original table 

7. now merge original table and qt tqble 

8.  now expand table  click on rounded(red) area 

by clicking red area open interface and from that unselect index column

press ok which look like these

9.  change qt table name to qt and select Id Articolo, code and qt and remove other column and it look like these

10. now filter table and which show those value non-null value 

and it look like these

here solution done 

don't get  confuse with values because i take some different value from you but following my step you got your answer here t attach step screenshort for comfortness 

close and apply from home tab and paly with it

Did i answer your question? Mark my post as a solution which help other people to find  fast and easily.

[123abc]

I apologize for the misunderstanding. Based on your clarification, you want the result where the code and quantity are in separate columns. To achieve this, you can use Power Query in Power BI or Excel with the following steps:

Assuming your original table looks like this:

```
CodeArticle | Material1 | Quantity1 | Material2 | Quantity2 | ...
--------------------------------------------------------------
A1 | M1 | 10 | M2 | 15 | ...
B2 | M3 | 20 | M4 | 25 | ...
...
```

You want to transform it into the following format:

```
CodeArticle | Material | Quantity
--------------------------------
A1 | M1 | 10
A1 | M2 | 15
B2 | M3 | 20
B2 | M4 | 25
...
```

Follow these steps:

1. **Load your data into Power Query**:
- In Power BI: Go to the Power Query Editor by selecting the "Edit Queries" option in the Home tab.
- In Excel: Go to the Data tab and select "Get Data" > "Combine Queries" > "Merge Queries."

2. **Use "Unpivot Other Columns"**:
- In Power Query, select the "CodeArticle" column and right-click, then choose "Unpivot Columns" > "Unpivot Other Columns." This will unpivot all columns except the "CodeArticle" column.

3. **Rename Columns**:
- Rename the "Attribute" column to "Material."
- Rename the "Value" column to "Quantity."

4. **Load the Transformed Data**:
- In Power Query, click "Close & Apply" to load the transformed data back into your Power BI dataset.
- In Excel, you can load the data to a new worksheet or overwrite the existing data, depending on your preference.

After these steps, you will have the desired result where the "CodeArticle," "Material," and "Quantity" are in separate columns.

[123abc]

To unpivot your table and transform it into the desired format with three columns (Code Article, Code of Material, and Quantity), you can use Power Query in Power BI. Here's a step-by-step guide:

Assuming your original table looks something like this:

| Code Article | Code Material1 | Quantity1 | Code Material2 | Quantity2 | ... |
|--------------|----------------|-----------|----------------|-----------|-----|
| A001 | M001 | 10 | M002 | 5 | ... |
| B002 | M003 | 15 | M004 | 8 | ... |

Follow these steps to unpivot your table:

1. Open your Power BI report.

2. Load your table into Power Query:
- In Power BI Desktop, go to the "Home" tab.
- Click on "Edit Queries" to open the Power Query Editor.

3. Select the columns that you want to unpivot (e.g., "Code Material1," "Quantity1," "Code Material2," "Quantity2," etc.).

4. Go to the "Transform" tab in the Power Query Editor.

5. Click on "Unpivot Columns" in the "Transform" tab. This will transform your selected columns into rows, creating new columns "Attribute" and "Value."

6. Rename the "Attribute" column to "Code Type" (or any other suitable name) and the "Value" column to "Code" (or "Quantity" if appropriate).

7. Close and apply the changes to load the transformed table back into Power BI.

Now, your table should be in the desired format with three columns: "Code Article," "Code Type" (which indicates whether it's a material code or a quantity code), and "Code" (which contains the respective material code or quantity).

Here's how the transformed table would look:

| Code Article | Code Type | Code |
|--------------|-----------|-------|
| A001 | Material | M001 |
| A001 | Quantity | 10 |
| A001 | Material | M002 |
| A001 | Quantity | 5 |
| B002 | Material | M003 |
| B002 | Quantity | 15 |
| B002 | Material | M004 |
| B002 | Quantity | 8 |

You can now use this transformed table in your Power BI report for further analysis and visualization.

[ancorte]

Anyone can help me ?

[ancorte]

Thanks 123abc, but the result that i want is different.

I want to have this result :

Code article/  Code Material / Quantity

In your example, i have in the Code column both "code material" and both "quantity material".

Instead code and quantity have to be in 2 different column.

I think that it's possible but i dont' know how.