Forum Discussion
Gathering data from Folder (excel files)
Nevermind guys, I have finally found the solution after looking for it for more than 6 months!
Please, have a look on this video https://www.youtube.com/watch?v=mZbD8aduIJU
It's exactly the problem I was facing (when having new columns plus having the right data under the exact column). In this case, where no data for that column I will get "null" which is exactly what I needed and most importantly, it's dynamic.
Final result:
Thanks for your time though, I truly appreciate it 🙂
Hello Anonymous
as already Anonymous has mentioned you can apply the Unpivot-function of Power Query. More specifically in your case the Table.UnpivotOtherColumns function. Just select all columns not to be unpivoted and choose this function from the menu
Here the complete solution
let
Source = #table
(
{"Article Num","Article Name","Destination","Code","202001","202002","202003","202004","202005","202006","202007"},
{
{"1","XXX","CA","A1","2","6","8","677","878","234","234"}, {"2","WWW","DE","B2","3","1241","234","43","7","455","765"}, {"3","RRR","US","C3","5","12","3","3567","43","234","555"},
{"4","TTT","CZ","C4","7","465","324","935","234","453","234"}
}
),
UnpivotOther = Table.UnpivotOtherColumns
(
Source,
{"Article Num", "Article Name", "Destination", "Code"},
"Year/Month", "Value"
)
in
UnpivotOther
Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hi Jimmy801 , Anonymous
Thanks for your promtp replies. Unfortunately, that's not the issue (I guess it's quite complicated to explain it by writing).
I am leaving here the XL sample files. Would it be possible for you to create a new query which grabs this files (3) from the folder and see by yourself what I meant, please?
Notice that whether you select to have the headers from the first file or the last one, except for Article Num Article Name Destination Code, the rest won't match (due to this moving week that happens with each new report).
I think it will be easier to keep diving into a possible solution (if any) from there where you will visualize the issue I am facing.
This is the link where the files are uploaded. I am sorry but I couldn't find another way to share them in here:
Thanks once again and please, let me know 🙂
Cheers.
Hello Anonymous
sorry, I don't get the point. You can use the Table.UnpivotOther and define your fixed column, like destination or code. Here the complete code. And there is nowhere specified a column name of your changing columns
let Source = #table ( {"WEEK","Article Num","Article Name","Destination","Code","202003","202004","202005","202006","202007","202008","202009"}, { {"202003","1","XXX","CA","A1","2","6","8","677","878","234","234"}, {"202003","2","WWW","DE","B2","3","1241","234","43","7","455","765"}, {"202003","3","RRR","US","C3","5","12","3","3567","43","234","555"}, {"202003","4","TTT","CZ","C4","7","465","324","935","234","453","234"} } ), UnpivotOhter = Table.UnpivotOtherColumns(Source, {"WEEK", "Article Num", "Article Name", "Destination", "Code"}, "year/month", "value") in UnpivotOhterIf this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyNevermind guys, I have finally found the solution after looking for it for more than 6 months!
Please, have a look on this video https://www.youtube.com/watch?v=mZbD8aduIJU
It's exactly the problem I was facing (when having new columns plus having the right data under the exact column). In this case, where no data for that column I will get "null" which is exactly what I needed and most importantly, it's dynamic.
Final result:
Thanks for your time though, I truly appreciate it 🙂
Hello Anonymous
fine you found the solution.
Problem was that I didn't get what you needed. You just spoke about unpivoting columns that are changing, not how to read them dynamically from differents sheets.
All the best
Jimmy