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 🙂
Community ChampionHello 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
UnpivotOhter
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
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 🙂
- Jimmy801
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
Thanks Jimmy801 ,
You're totally right, it was very confusing and I didn't know how to explain it better. Need to improve in that if I want to get help in the future!
Like I said, appreciate the time invested to try to help me anyways 🙂Have a good mate!
Cheers.
- Jimmy801
Thanks for the Feedback
the most important thing, and also the most difficult thing is to describe really the content of what is really needed. Because when we are talking about m-language its programming and it's crucial for somebody that want to help that the goal is cleary specified. And I know how hard it can be to describe this.
have a nice day
Jimmy