Log In
Register
Home
Active Member
December 5, 2018
Offline
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
See Answer
July 16, 2010
Offline
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
Active Member
December 5, 2018
Offline
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
July 16, 2010
Offline
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
Answers Post
Active Member
December 5, 2018
Offline
Exact ! It's what I did and it works perfectly.
Thank you for your help
Active Member
December 5, 2018
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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.
Active Member
December 5, 2018
Offline
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
1 Guest(s)
