• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Unpivot table from a monthly file (column name change)|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Unpivot table from a monthly file (column name change)|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryUnpivot table from a monthly file (…
sp_PrintTopic sp_TopicIcon
Unpivot table from a monthly file (column name change)
Avatar
Hughes WILLIOT

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
December 5, 2018
sp_UserOfflineSmall Offline
1
December 5, 2018 - 8:59 pm
sp_Permalink sp_Print

Hi, 

 

I'm working on a monthly report file for my dashboard. I would like to place the monthly report in a folder in order to be up to date. 

One of the column name is i.e November Color and another one is November Mono. Next month it will be December Color and December Mono. 

In Power BI, I go to get data from folder, unpivot table to get November Color and November Mono part of the row to be able to analyse my data. 

Problem is when I place the December report in the folder, the system says : cannot find November Color column .... which seems normal 🙂

I was trying not to use my report file as header but the tab header (column 1, Column 2, ...) but of course when I go to the unpivot, I unpivot the column #x and not November Color column..... 

 

Could someone help me or meet already this kind of issue.

thank you so much for your help.

Cheers

Hughes

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4450
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
December 5, 2018 - 10:12 pm
sp_Permalink sp_Print

Hi Hughes,

You can reference the columns by position. There is an example in this forum Q&A. Note: Power Query is zero based so the first column is number 0, the second column is number 1 and so on.

Mynda

Avatar
Hughes WILLIOT

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
December 5, 2018
sp_UserOfflineSmall Offline
3
December 6, 2018 - 3:16 am
sp_Permalink sp_Print

Thank you so much Mynda. It's work. 

Anyway, I was able to place the next monthly report in the specific folder, click on refresh ..... and unfortunately, the header of the report is in the middle of the two reports  🙁 then when the system tries to convert the column to a date format and it falls on the header name .... error ! 

Do you know why the header of the file is in the middle of the two file... I can imagine if I place the next report in the specific folder again for the month after, I will have a new header....

When I did the transformation, it was based on Table without headers then I've column 1,2,3 ... and during the transformation, I said to the system to use the first row as header. 

 

Thank you for your help. It's not easy as your training 🙁

Cheers,

Hughes

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4450
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
December 6, 2018 - 2:57 pm
sp_Permalink sp_Print

Hi Hughes,

I think I'm missing some details on the process you have followed.

You might be able to fix your existing query by adding a step before the Change Type step that filters out the headers, assuming there is at least one column in the files that has the same name or contains the same text e.g. Filter > Text that Does Not Contain > 'Color'.

Mynda

sp_AnswersTopicAnswer
Answers Post
Avatar
Hughes WILLIOT

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
December 5, 2018
sp_UserOfflineSmall Offline
5
December 9, 2018 - 12:14 am
sp_Permalink sp_Print

Exact ! It's what I did and it works perfectly. 

Thank you for your help 

Avatar
Hughes WILLIOT

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
December 5, 2018
sp_UserOfflineSmall Offline
6
January 12, 2019 - 3:22 am
sp_Permalink sp_Print

Hi Mynda, 

 

First of all, happy new year and all the best for 2019 !

 

I've still questions about unpivot table and maybe you can help me. 

Coming back to first problem, I've a monthly file with header which contains this for instance : "November Color" and another one "November Mono" for November month. Then, getting data from folder when it's December, I will place the file in the folder and so on.... 

I've a many row in this file and then for each printer, I've the number of pages printed out for each device. I would like an analyse on the number of pages printed out per month and per category color or mono. Then, I've to use the function unpivot on the columns "November Color" and "November Mono". In that way, I've not 2 row for each previous row. That is working, no problem. 

What I thought was the system is doing that file per file, which is not true. In fact, the system is doing it's : it loads all file from the folder first November, December .... and applies the steps like unpivot. As the header is November from the first file loaded when it is doing the unpivot, it changes all data in the column by November even if the file is for December, January ..... which is not good.

 

Do you have a tip to do that and to keep the month per file which will allow me to do my analyse per month ? 

I don't know if I'm clear. Let me know if you have question. 

Cheers,

Hughes

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
7
January 12, 2019 - 3:24 pm
sp_Permalink sp_Print

Hi Hughes,

Sounds like you have an extra step that renames some headers?

When you import from a folder, you should see a list of files found in that folder in the first steps. When expanding the files content, you can keep the column containing the source file name, this is usually a good practice to keep track of the source of the data.

Avatar
Hughes WILLIOT

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
December 5, 2018
sp_UserOfflineSmall Offline
8
January 13, 2019 - 3:21 am
sp_Permalink sp_Print

Hi, 

 

Thank you for your prompt answer. 

 

However, my problem is "November Color" & "November Mono" must be in the header for the unpivot to get 1 line for each of them for each device. 

Look the structure of the file :

Header is :                        SN# | November Color | November Mono

Row#1                            1234  | 5000                | 10000

Row#2                            9876  | 2500                | 4600

 

What I would like to get with the unpivot, in order to do my analyze month per month :

Row#1                           1234   | 5000               |November Color

Row#2                           1234   | 10000             | November Mono

Row#3                           9876   | 2500              | November Color

Row#4                          9876    |4600               | November Mono

 

This is working but when I placed a new file in the folder for December, when the system is doing the unpivot, it changes all the column with November even if you are talking about data from December file. 

 

Does it help ? 

Cheers,

Hughes

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Brian Pham
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 205
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
stuart burge
Bruce Tang Nian
Scot C
Othman AL MUTAIRI
Misael Gutierrez Sr.
Attif Ihsan
Kieran Fee
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Forum Stats:
Groups: 3
Forums: 24
Topics: 6223
Posts: 27295

 

Member Stats:
Guest Posters: 49
Members: 31920
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.