Forum Discussion
Notepad to Excel - Data Transfer
You can use Power Query to import the data as it seems you did, but at the end, instead of loading to a table, select "create connection only" and "Add to the data model". This way the data will not be visible in the worksheet but is available in the memory for data analysis using pivot tables.
Here is a tutorial: https://www.youtube.com/watch?v=5u7bpysO3FQ
You can use Power Query to import the data as it seems you did, but at the end, instead of loading to a table, select "create connection only" and "Add to the data model". This way the data will not be visible in the worksheet but is available in the memory for data analysis using pivot tables.
Here is a tutorial: https://www.youtube.com/watch?v=5u7bpysO3FQ
You are very welcome! 🙂
Please give it a try and if it solves your problem, please remember to mark my answer as a solution to your question. Thank you.
- Celia_Alves
Yes, that worked when importing the data to Excel using a 'Data Model' - thank you!
However, I am having difficulties manipulating the data in a Pivot Table to get the info I need - I am looking for sum of values however I can only get the count as "it is not a supported calculation for Text Data types"
Do you have any thought on how to overcome this issue?
Many Thanks,
AlexAs SergeiBaklan explained, you need to make sure that you define each column Data Type correctly on Power Query before importing your data into Excel, so that Excel knows what is a number and what is text.
For a thorough lesson about this matter, feel free to check this video: https://youtu.be/xbyN9iJhlso