Forum Discussion
POWER QUERY - 365 COLUMNS INTO ONLY 1
juan jimenez hello there,
Yes, you can do this. From Excel, select any cell in your Table. Go to the 'Data' tab, click 'From Table/Range' in the 'Get & Transform Data' group. This will pop up the Power Query Editor (PQE). Select all of your columns (with the left-most column selected by default, hold the SHIFT key and click the right-most column). Select the 'Transform' tab and click 'Unpivot Columns'. That's it, you're done. Header values are in the first column, values are in the next. I would rename the headers to suit your needs.
This is good for your example, and is a good way to understand how it works. However, you specified you had 365 column headers. If you look at your query, in the PQE you'll see how each columns name was hard-coded. This doesn't make it dynamic. It's like the macro recorder where it spits out everything exactly as you did it with zero interpolation. To make this dynamic, you need to edit a bit of the M code in the 'Advanced Editor' ('View' tab in PQE). The code below just needs your Table name (I used the one from your sample file) and it will unpivot all columns to the desired result.
let Source = Excel.CurrentWorkbook(){[Name="Tabla1_2"]}[Content], Columns = Table.ColumnNames(Source), Unpivot = Table.Unpivot(Source, Columns, "Date", "Value") in UnpivotHTH
juan jimenez hello there,
Yes, you can do this. From Excel, select any cell in your Table. Go to the 'Data' tab, click 'From Table/Range' in the 'Get & Transform Data' group. This will pop up the Power Query Editor (PQE). Select all of your columns (with the left-most column selected by default, hold the SHIFT key and click the right-most column). Select the 'Transform' tab and click 'Unpivot Columns'. That's it, you're done. Header values are in the first column, values are in the next. I would rename the headers to suit your needs.
This is good for your example, and is a good way to understand how it works. However, you specified you had 365 column headers. If you look at your query, in the PQE you'll see how each columns name was hard-coded. This doesn't make it dynamic. It's like the macro recorder where it spits out everything exactly as you did it with zero interpolation. To make this dynamic, you need to edit a bit of the M code in the 'Advanced Editor' ('View' tab in PQE). The code below just needs your Table name (I used the one from your sample file) and it will unpivot all columns to the desired result.
let
Source = Excel.CurrentWorkbook(){[Name="Tabla1_2"]}[Content],
Columns = Table.ColumnNames(Source),
Unpivot = Table.Unpivot(Source, Columns, "Date", "Value")
in
Unpivot
HTH
It works!!!
Thank you very much.
Some cells in a column are empty. I would like to delete the entire rows of that cells that are empty.
Do you know how could I do it?
juan jimenez you can absolutely do that in the query. Just change it to this...
let Source = Excel.CurrentWorkbook(){[Name="Tabla1_2"]}[Content], Columns = Table.ColumnNames(Source), Unpivot = Table.Unpivot(Source, Columns, "Date", "Value"), FilterBlanks = Table.SelectRows(Unpivot, each [Value] <> null and [Value] <> "") in FilterBlanksTo do it manually, as opposed from just copying/pasting the above text into the Advanced Editor of Power Query, you would select the filter drop down in the 'Value' column and de-select blanks. This can be difficult if you don't currently have any blanks, which is why it's more dynamic to write in the line like I did above. This will take care of it regardless if there are blanks currently or not.
HTH
- it works. thank you very much!