Table.Unpivot

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Table.Unpivot

What is the Table.Unpivot Function?

The Table.Unpivot function is a part of the Power Query M language. It is used to transform wide tables into long tables by unpivoting columns. When you unpivot a table, you essentially take the data from the column headings and turn them into rows. This makes it easier to analyze and visualize the data.

To use the Table.Unpivot function, you need to have a table with at least two columns. One column should be the identifier column, and the other columns should be the data columns. The data columns should all be of the same data type.

How to Use the Table.Unpivot Function

To use the Table.Unpivot function, you need to follow these steps:

1. Select the table you want to unpivot.

2. Go to the Transform tab in the Power Query Editor.

3. Click on the Unpivot Columns button.

4. Select the columns you want to unpivot.

5. Click on OK.

Once you have clicked on OK, Power Query will generate the M code for the Table.Unpivot function. You can then edit the M code to customize the transformation to your needs.

The M Code Behind the Table.Unpivot Function

The M code behind the Table.Unpivot function is relatively simple. The function takes two arguments: the table to be unpivoted, and the column names to be unpivoted. Here is the basic syntax for the Table.Unpivot function:

Table.Unpivot(Table as table, ColumnNames as list)

The `Table` argument is the table you want to unpivot, and the `ColumnNames` argument is a list of the column names you want to unpivot. Here is an example of how to use the Table.Unpivot function:

let

Source = Excel.Workbook(File.Contents("C:UsersuserDesktopdata.xlsx"), null, true),

Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),

#"Unpivoted Columns" = Table.Unpivot(#"Promoted Headers", {"Column1", "Column2", "Column3", "Column4"}, "Attribute", "Value")

in

#"Unpivoted Columns"

In this example, we are unpivoting the columns `Column1`, `Column2`, `Column3`, and `Column4`. The unpivoted columns are then renamed as `Attribute` and `Value`.

Customizing the Table.Unpivot Function

You can customize the Table.Unpivot function to fit your specific data needs. For example, you can rename the unpivoted columns, or exclude certain columns from the unpivot.

Here is an example of how to exclude certain columns from the unpivot:

let

Source = Excel.Workbook(File.Contents("C:UsersuserDesktopdata.xlsx"), null, true),

Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),

#"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Column5"}),

#"Unpivoted Columns" = Table.Unpivot(#"Removed Columns", {"Column1", "Column2", "Column3", "Column4"}, "Attribute", "Value")

in

#"Unpivoted Columns"

In this example, we are excluding `Column5` from the unpivot.

The Table.Unpivot function is a powerful tool in Power Query that allows you to transform wide tables into long tables. By understanding the M code behind the function, you can customize the transformation to fit your specific data needs. Whether you need to rename columns or exclude certain columns from the unpivot, the Table.Unpivot function can help you get the data you need for your analysis and visualization.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)