Unpivot Excel
Paciente Banfield
We can easily convert tabular data into a crosstab format using a PivotTable. But here, we want to do the opposite. We want to unpivot the data, converting it from a crosstab format into a tabular format.
Note: please note that unpivoting the data is not the same as transposing it. Transposing the data would place departments in rows and accounts in columns. If you need to transpose instead of unpivot, check out this Excel University blog post instead.
Now that we have identified which columns to unpivot, we can use the unpivot command. The unpivot command is located on the Transform tab. Since the Ribbon dynamically sizes itself based on the dialog size, you may see the unpivot columns command with a text label, like this:
Dear all,
For one of my market analysis projects I'm looking for someone with a bit more experience with Power Pivot and Power Query in Excel.
I have been able to successfully combine separate files through power query on the one hand and I have been able to unpivot part of a table through power query as well. However for a quarterly market update I'm now looking to combine both steps.
So every quarter I receive an excel file with market data on the past 3 months. I have uploaded an (dutch) example for Q1 in 2018 where I have removed the product names. As you can see the first column contains the product names and the next three each refer to a month in the quarter.
Once unpivoted I have a nice and raw database. However since we get quarterly updates, I'll have to update the file each time. I figured it might be possible to tell powerquery to always unpivot each file on columns 2, 3 & 4 and then combine them. But I'm not sure how (nor if) that's possible.
So perhaps someone here can help?
Thanks in advance, much appreciated! :)
Ps. my file and excel is in Dutch, but power query is in English so directions in either language is OK for me! :)
I am familiar with 365 Get & Transform. However, I am still feeling my way around a few items that I can't seem to wrap my head around and need some guidance. In the below scenario how could I unpivot this data set so it extracts the first 3 characters from each column header (so it creates a month column from it and removes it from the field name, hence extract). Then instead of 36 columns of data it refreshes to only 3 columns. See example attached
I have files that have different data on the rows. See original file format below. I have multiple files loaded in my PBX file so far. I have created a Category column which I want to use as the column headings. My issues is that when I unpivot I never get whay I need. I required the format on the right 'Required Format'.
I got the pivot to work. However when there are two sets of data in a file then I get an error "There were too many elements in the enumeration to complete the operation". This happens because there are now duplicates in the Source.Name and the Category columns. I only get the error for file1. I have tried to add an index column which removes the error but the data is then on different rows. I was thinking if instead of an index column maybe a row number for each file might help.
I tried to add an index column in the Query Editor before unpivoting the columns then removed the duplicate index numbers but no matter what order I did the steps in, I always ended up losing data relating to attempt 2 and 3. Pls help me solve this.
Hi,
I am takeing chanse to replay on this one, and want to ask does thid line work? When I go line by line I see that there is no "null" or "empty" in previous step, but I do get "null" in result table, ex. B Attempt 2 08/04/2019.
I have sales data being populated from users in Power Apps that feed into excel. That excel data is then being used to populate a Power BI report. The initial issue I had is monthly data is populating across rows in excel as opposed to columns so I used Power BI to unpivot the sales figures so that I can use Months as different filters in Power BI.
The totals work properly for items only if an indivual month is selected. If all months are selected the grand total is 5x+ what they should be. I believe the issue is caused by data being unpivoted or maybe I need a specific formula in the measure. Please let me know if you can point me in the right direction for a fix.
Try out Data Factory in Microsoft Fabric, an all-in-one analytics solution for enterprises. Microsoft Fabric covers everything from data movement to data science, real-time analytics, business intelligence, and reporting. Learn how to start a new trial for free!
Data flows are available both in Azure Data Factory and Azure Synapse Pipelines. This article applies to mapping data flows. If you are new to transformations, please refer to the introductory article Transform data using a mapping data flow.
Use Unpivot in a mapping data flow as a way to turn an unnormalized dataset into a more normalized version by expanding values from multiple columns in a single record into multiple records with the same values in a single column.
The Unpivot Key is the column that the service will pivot from column to row. By default, each unique value in the dataset for this field will pivot to a row. However, you can optionally enter the values from the dataset that you wish to pivot to row values.
Setting the Column Arrangement to "Normal" will group together all of the new unpivoted columns from a single value. Setting the columns arrangement to "Lateral" will group together new unpivoted columns generated from an existing column.
Right, so use a Folder query and in that folder query you can do your unpivot operations on an example/sample file and it will then perform those operations on all files and then append everything together.
Greg, thank you for you comments about un-pivot and combine when using get data with a folder. I am not able to make this work in the situation described in this thread by smurakam0201. If all three files had identical column headers this would be a piece of cake. However, because the headers in columns B, C, and D are different in each file, if I perform the un-pivot with the data as it is first imported, the headers are Column1, Column2, Column3... and the un-pivot yeilds meaningless data. If I promote the first row to the header, then it works perfectly on the sample file but fails on the other two files as it is looking for a column header that doesn't exist in the other two files. This is a very common need for anyone doing forecasting in which each month a new file is produced with the same number of columns but with the YrMonth column headers shifting by one month in each file (dropping the oldest month and adding the newest). Any help you can provide to solve this would be greatly apreciated!
Where did this error occur in steps of first image? Did this cuase by other file (like .txt or other file which not excel)? If so, you could filter it in invoked table like below to see whether it work or not
I've got a situation that I cannot figure out that I'm hoping someone else has run into. I've got a table of sales information with months across the columns. Usually this would be a quick Unpivot when I bring in the data, however I've got both Sales Amount and Sales Quantity which have their own group columns. Here's a very oversimplified example, but it gets the point across:
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@ImkeF I've gone through this and I think I understand it. The only issue that I'm having is this will be a live connection through Access, so the parts about the CSV file wouldn't apply to my situation (at least if I understand this...). I know you can probably apply the same logic to the query through Access, but this is my first foray with M so I'm pretty lost. Could you help me out with preparing the "Prepared Table" using an Access file?
I have a similar issue. I've got a set of data that's exported from a database to an Excel file. I can't modify that source Excel file so all of my transformation needs to be done in Power BI Desktop.
In the source data, each row is one parent, but there can be one or more children per parent/row. The children's information is pivoted into multiple sets columns on the right of the table. Here's an example set of data I made up:
What I need to do is convert this from one line per parents to one line per child. This seems like a perfect use case for the unpivot transformation tool, but I can't get it to work for multiple sections of columns like this.
One complicaiton to this is that instead of 3 potential children/students per parent, there are up to 7. Because it would be such a bear, I'm scared to know what a custom M/DAX solution would look like. I'm really hoping there's some way to use the unpivot tool that I'm not aware of.
Anybody know of a way to do this using the Excel or Power BI graphic UI?
I have this issue often and am not the best at using M (certainly not writing it), but if there's some sort of re-usable M "template" I could use, that would work as well.