level: Intermediate
I have written a couple of posts here and here about the problems and dangers of using Power Query with Power Pivot. Well I am very pleased to say that I now fully understand the problem, how it is caused and how it can be avoided.
Edit: Nov ’15. Microsoft has released a fix to prevent this problem occurring.
First a Clear Statement of the Problem
You use Power Query to create a table and load it into Power Pivot. You only load a subset of columns to start with.
All is fine for a while and you create a whole lot of DAX formulae – life is sweet. You add new rows to your Power Query Table and refresh the data into Power Pivot – not a care in the world.
Then one day you need to make a change in Power Query (eg to bring in another column of data that you left out originally). You go into Power Query and make a change to your table to bring in a new column.
You save, close and load and then BAM – you get the following dreaded error message. You didn’t even see it coming before it hit you right between the eyes.
Exception occurred. (Exception from HRESULT: 0x80020009 (DISP_E_EXCEPTION))
The only option now is to remove the table from Power Pivot and then reload it. When you do this you lose all your relationships, all your measures in the table, all your calculated columns you created in the table (not that you should have any of those – right?!)
What causes this?
It is very easy to prevent this happening if you know what causes it. This is one of the many high value things I learnt at the PASS BA Conference in San Jose in April 2015 (in 2 separate sessions – one by Chris Webb and one by Miguel Llopis) . So what causes the problem? The issue is caused when you make structural changes to the table in BOTH Power Query and Power Pivot.
Let’s work through the problem.
The Connection
When you first load data from Power Query into Power Pivot, a ‘connection’ between to the 2 applications is created. You can see this connection from inside Excel on the Data Tab. See example below. Note how the connection string can be selected with the mouse.
Then you innocently make a change in Power Pivot without realising it is a problem
This is where the real problem is caused. If you make ANY CHANGES inside of Power Pivot to the structure of the original Power Query table, then Power Pivot will change the connection from being “editable” to being “read only”. If you then try to make any structural changes to Power Query AFTER the connection is set to read only, this error is then triggered. Once the connection is read only, the only thing you can do is “refresh” the model to add new rows of data. No further structural changes can be made to the table in Power Query after the connection is set to ‘read only’.
All of the following changes in Power Pivot will trigger the problem (careful, there may be more).
- Changing the table name in Power Pivot
- Changing an imported Column Name in Power Pivot
- Deleting a column in Power Pivot
Note that it is quite OK to create a calculated column inside of Power Pivot (although I wouldn’t recommended it – that is what Power Query is for).
And a big part of the problem is that you make the change inside of Power Pivot and you don’t even realise there is a problem – there is nothing to warn you or tell you that the connection has been changed to read only. It could be weeks or months later that you make the change to Power Query and trigger the dreaded message. By this time you have completely forgotten about the change you innocently made in Power Pivot.
So how do you know if you have a problem?
So by the time you get to this point, you probably realise that you may already have a problem that you don’t know about. To check, you need to take a look at the connection string between Power Query and Power Pivot. The easiest way to do this is is to goto ExcelDataConnectionsPropertiesDefinition and check the connection string (just like the example in the previous image). If you see that the connection string is ‘greyed out’ – ie not selectable and also there is a message stating properties can’t be changed, then this confirms you have a problem and it is already too late.
Time to move on with confidence
I have to say that since I have learnt the problem, I have re-engaged with Power Query like never before. I now use Power Query with confidence as a data provider to Power Pivot and I have not had 1 single instance of a problem since. The simple solution is to never ever make any changes inside Power Pivot to a table you have imported from Power Query. Take this precaution and you will never have a problem again. You can make all the changes you need directly in Power Query as long as you only use Power Query and never make structural table changes in Power Pivot. There is nothing you can’t do in Power Query anyway, so this is not a limitation in any way.
I hope this post will give you the confidence to do the same.
If you want a comprehensive lesson on how to use Power Query, checkout my training course here https://exceleratorbi.com.au/power-query-online-training/
What I actually care most in regard to is wellness
issues. By myself, I sign up for journals dealing with this subject
matter, and I retain educated about the most recent wellness research.
In what way is this relevant? Personally I think there is no more essential focus
for my energy. Also, this blog page seems as though it’s worth the time to check out again. I
dig through tons or even more of websites every week.
Honestly, my backbone typically is uncomfortable and I need a fresh new pursuit.
lol Anyhow, I think if almost everyone wrote about their place in life, and did it articulately, we’d have a more robust Earth.
This is definitely not the first moment in time I spotted
a blog site that was actually worth commenting on. This actual article
is important to me. I really live a life-style of plenty of ways; that is to say that my work routines vary.
I perform Perinatal Massage. In addition,
I buy and sell antiques. I’m understanding the concepts of Direct Admin. And, that’s merely a handful of activities I undertake.
Anyway, my idea is, each of us all considers a niche
from a distinct standpoint. Just about every experience all of us have
forms us and each of our view of our own selves and the entire
world all around us all. I believe finding some larger sense of actuality is just where every little thing many of us converse about
leads to. So, just how do people afterward interact?
Is it actually possible? I think each of our personal contributions to the
Web Commons alters countless individuals, possibly.
Hence think about that, bloggers and folks writing comments.
Your own personal opinion could very well support some other
person. Or even, comments can do the antipode. Not to mention, everyone, meaning,
you, me, genuinely needs to pay attention to
attempt to assist other individuals to develop and certainly not suffer.
Thanks a lot for this blog site; I am submitting this site along with my other beneficial websites I’ve come across that I actually troubled to leave feedback on. Continue to never stop writing!
Continue to keep expressing!
I have a job as an Event Massage Coordinator. I without exception think on the theme of just what got men and women setting about
blogs. In the end, a blogger has something meaningful to share,
whether cheeky, insightful, educational, or in any other case compelling to some people.
I suppose quite a lot of writers aim to aid other people. Naturally, right now
there are truly those driven to the mode of reflection for the
reason that it may possibly be lucrative and develop directly into a full-time gig.
I read a lot more blogs in a month in comparison with anyone in my state, I can easily
promise you. I think that’s just about all excessively obscure, actually.
Every single blog writer will begin submitting for a diverse, one of a kind rationale.
In my line of employment, ladies organise a chair Event Massage just for both typical
(birthday celebration, commercial event, etc.) causes, in addition to exclusive motives including a Spiritual motive.
How do several writers persist, even while other folks let go?
It’s actual enthusiasm! An interest for creating, a passion for helping or helping to
make us have fun or particularly which I don’t know.
A fervor for sharing stories for quite a few, I am confident.
Existence really does have meaning, nevertheless people
have to succeed at comprehending it. I really feel operating
a blog could very well perhaps be a sort of Self-Knowledge
undertaking aiding hidden crowds of folks. Certainly, there are blogs and forums created by K-mart and Gimbles
(decided on defunct retailers purposefully! hehe) however I
really mean substantial blogs. Personalized sites.
Small company blogs. Class weblogs. Nonprofit information sites.
Information sites with love.
I am generally looking for brand spanking new details on this kind of crucial theme, and am specifically delighted when ever I discover sites which might be well-written and well-researched.
Thanks for offering this superb information, and I take
a look forward to read more right from your weblog in the long term future.
Women and men at the moment really should try to study a tad bit more regarding
these particular information, although there seems to in no way end up being a sufficient
amount of hours. Following available free time for friends and family, and of course carrying out work, what spare time is left
to devote to mastering some of these most crucial teachings?
As an individual practicing post-natal massage, alongside developing web
sites and administering a lot of nodes, spare time is most definitely a thing which i really don’t possess enough of.
With some luck, I am capable of finding the spare time necessary to study this significant
area of interest more in-depth. Thanks to you for supplying this particular
notable resource.
This is not the first time I came across a blog that was
deserving of writing comments on. This particular post
is excellent to me personally. I basically live a personal life
of numerous modes; that is to say that my employment functions range
the gamut. I carry out Postnatal Massage. Additionally, I buy
and sell antiques. I’m getting to know Centos8. And, that’s
merely a couple of activities I conduct. Anyway, my idea is, each one
of us approaches a niche from a distinctive point of view.
Pretty much every encounter we have forms us and each of our
viewpoint of ourselves and the globe around you and me.
I suppose identifying some greater experience of actuality is where every little thing many
of us speak regarding brings us to. So, exactly how can men or women then correspond?
Is it even possible? I believe our own own personal contributions to the Internet Commons alters
a lot of citizens, often times. And so contemplate that, bloggers and everyone writing comments.
Your feedback could quite possibly help someone else.
Or, remarks can easily do the complete opposite.
Plus, everyone, meaning, you, me, genuinely will want to pay
attention to attempt to guide other folks to grow and in no way suffer.
Thank you for this specific weblog; I personally am logging this blog with my other first-rate websites I’ve discovered that I even bothered to comment on. Continue to keep
authoring! Continue to keep expressing!
This is not really the very first moment in time I uncovered a blog site that was deserving
of participating in a comment section on. This actual posting is significant to me personally.
I truly live a way of life of several modes; that is to
say that my job functions range the gamut. I
practice Postnatal Massage. On top of that, I buy and
sell antiques. I’m figuring out Centos8. And, that’s merely
a handful of activities I conduct. At any rate, my idea is, each one of us approaches a subject matter
from a exclusive mind-set. Virtually every experience all of us
have patterns us and each of our viewpoint of our own selves and the planet all around us.
I think seeking some form of superior sense of reality is exactly where all the things most of us talk regarding brings us to.
So, precisely how will individuals then interact? Is it even possible?
I believe all of our personal contributions
to the Internet Commons transforms a lot of folks,
sometimes. Therefore think about that, bloggers and commenters.
Your opinion can potentially support another individual.
Or perhaps, feedback can certainly achieve the contrary.
Plus, everyone, meaning, you, me, seriously have to care to attempt
to guide other people to improve and certainly not decline.
Bless you for this blog; I personally am logging this blog along with my various other superb weblogs I’ve discovered that I even bothered to opine on. Continue to never
stop writing! Keep revealing!
I occasionally come upon web sites similar to this at the most unexpected occasions.
Like at this time, I was trying to find something on an entirely different subject matter and I
stumbled upon this blog instead. I more often than not appreciate hunting the net
for articles, but yet normally I prefer to be out of doors, walking in the neighborhood forest
land. Few things are similar to outdoors to
help you feel energized. This is when I am not functioning as
an at home massage therapist. That occupies a lot of my personal precious time.
Once I am scannnig websites, frequently I like to take a
look at content articles which I find. Oftentimes, I’m able to
come across innovative guidance, or perhaps a modern means of taking a look at things that I had not
known about previously. In such a situation, I am usually appreciative.
Simply because I might not have opportunities to search on-line much, because of my job as
an in-home massage therapist, I endeavor to maximize my activities
on-line.
#file[Blog_Comment.dat
In real life, most subject areas are a great deal more elaborate than some typical viewer might conclude,
in accordance with their vantagepoint. I’m not declaring that am an authority on this
specific subject being talked about, consequently I think it’s for alternative site members to bear in mind.
I am definitely not hoping to make problem or be bothersome.
Alternatively, I recognize from experience that this could be the
event. I am educated in Perinatal Massage, and in my very own targeted profession, I find it quite a lot.
Just-graduated Postnatal Massage Practitioners are likely
to exaggerate claims; which can be, these individuals don’t yet truly appreciate
the limits of their particular “scope of practice,” and as a result they could perhaps come up with statements that are overbroad when discussing with patients.
It’s the same exact occurrence; they have been minimally schooled in a theme, don’t understand the full breadth of
it, and now presume they are the Advisors.
People young and old now a days would like to learn a whole
lot more with regards to these types of topics, even though there will under no circumstances end up being a lot of time.
After time for relatives and buddies, and certainly doing business, exactly
what time period is left to dedicate to trying to learn these types of critical teachings?
As an individual doing perinatal masage therapy, along with constructing sites and
handling a lot of machines, time is in fact a commodity that I
don’t currently have ample amounts of. Most likely, I will be able to find the hours necessary to analyze this most important matter more exhaustively.
Many thanks for offering this specific special source of information.
Consumers in modern times need to realize considerably more concerning any of these
subjects, though there will by no means be a sufficient quantity of precious time.
Right after time for friends and relations, and
naturally having a job, just what time is left to
expend to being taught these most vital teachings?
Being an individual applying perinatal masage therapy, besides producing web sites and managing
many different nodes, available free time is unquestionably
a thing that I usually tend not to possess a lot of.
With some luck, I’ll be capable of finding the time necessary to analyze this really important concept more honestly.
Thank you for sharing this unique very good learning resource.
Grown ups right this moment really should try to master very much more on the subject of such topics, though there seems
to never turn out to be quite enough time. Right after time for friends and family, and naturally being employed, precisely what spare time is left
to devote to researching these particular crucial teachings?
As an individual practising postnatal massage, as well as constructing websites and handling several machines,
time is most definitely a commodity i normally do not currently have enough of.
With luck, I am capable of finding the energy important to master
this major subject matter more thoroughly. Many thanks for furnishing this kind
of remarkable author resource.
In reality, most information are a tad bit more difficult than a amateur observer might
possibly conclude, in line with their vantagepoint.
I’m not saying that I happen to be an authority on the following subject
at-hand, as a result I know it’s for alternative message board subscribers to take into consideration. I am definitely not making an attempt to
make difficulties or be unacceptable. Rather, I recognize from experience that the aforementioned could be the event.
I am trained in Postpartum Massage, and in my personal targeted discipline,
I witness it considerably. Brand-new Postnatal Massage Therapists are prone to overstate claims; which
can be, these people don’t at this point seriously recognise
the limitations of their “scope of practice,” and thus they may likely come up with statements that are too general when engaging with patients.
It’s the very same occurrence; they have been brought in to
a theme, don’t fully understand the full breadth of it, and now
(wrongly) conclude they are the Authorities.
I just can’t believe anything we get irritated about in our
lives makes a difference. Continuously squandered thinking about the different things that people deal with every single
working day is simply undoubtedly necessary, as no other person will probably confront existence for any of us.
I feel that people young and old need to be more sane.
Research is wonderful, but so much of whatever you might know about is so abstract.
Having a cup of tea is a zen process that brings one back
again to the present moment. We must be more in the here and now,
without being confused by life and the theatre we experience each day.
Folk in the present day really should study way more pertaining
to any of these subjects, although there
seems to by no means end up being adequate available free time.
Following precious time for family, and naturally doing work, just what period of
time is left over to expend to learning many of these important theories?
Being an individual practising postnatal massage therapy,
in conjunction with building web pages and
dealing with a number of computers, available free time is absolutely a
commodity that we commonly do not currently have enough of.
With luck, I am capable of finding the period of time necessary to research this fundamental issue more thoroughly.
Thank you for dispensing this advantageous aid.
I actually have a tendency to come across articles similar to this
at some of the most improbable occasions. Such
as today, I was seeking something on an utterly
different area and I stumbled upon this blog instead.
I commonly enjoy scouting cyberspace for article content, but yet generally I want to be out in the open, trekking in the local forest land.
You’ll find nothing is like fresh air to cause you to truly feel
invigorated. This is when I am not doing work as an at
home massage therapist. That occupies the vast majority of
my energy.
I am definitely looking for new info about this
kind of significant issue, and was especially delighted when I
actually find blogs that happen to be well-written and well-researched.
Thank you for offering this wonderful data, and I glimpse forward
to learning much more via your blog page in the long term.
Being prejudiced will never be smart. Each time I disclose I am a
Massage practitioner, actually does the fact have a
bearing on exactly how individuals see me personally? I
am certain it will. Why don’t you consider in the event that I declare I work as a Mountainside Massage?
Would that invert your actual representation of me personally?
By using every individual brand-new simple fact that you come across, your entire impression could very well
transform. For sure, there could be under no circumstances
any kind of “last word” relevant to exactly what we tend to understand; most of us can certainly at all times gain knowledge of a tad bit more in regards to another person or maybe something.
Think of that. The idea is absolutely not “being suggestible” the instant people
subtly alter our ideas with the help of additional knowledge.
(If ever an individual has not ever heard about a Mountainside Massage bodywork, you’ll find
it’s a STRONG clinical technique available to Moms-to-be.)
Whenever I am reading online, at times I like to browse through posts which
I find. Typically, I can also come across cutting edge
material, or simply a fresh manner of studying things that I
hadn’t known about before. When this happens, I’m always grateful.
Considering that I might not have a chance to search online a lot, as a consequence
of my job as an massage in home therapist, I attempt to maximize my activities on-line.
I occasionally come upon internet sites such as this at
the most impossible moments. For example currently, I had been on the lookout for something on an utterly different issue and I discovered this site in its place.
I frequently prefer searching the world wide web for article content, but generally I prefer to be outside the house, walking
in the area woods. Absolutely nothing is similar to outdoors to make you truly feel re-energized.
This can be when I am not being employed as an at home massage therapist.
That occupies just about all my own precious time.
I have been browsing on-line more than 3 hours these days, but I by no means found any interesting article like yours. It’s beautiful worth sufficient for me. In my opinion, if all web owners and bloggers made good content as you probably did, the web can be much more helpful than ever before.
In reality, the majority of questions are very much more
problematic than a common casual viewer could conclude, in line with their
point-of-view. I’m not stating that I happen to be a specialist on this idea being outlined, as a result I
guess it’s for different online community people to look
at. I am definitely not attempting to start trouble or be infuriating.
Alternatively, I fully understand from knowledge that the
aforementioned can be the circumstance. I am properly trained in Postnatal
Massage Therapy, and in my very own targeted discipline, I
witness it quite a lot. Brand-new Postpartum Massage Practitioners are likely to exaggerate claims; that is,
they don’t yet definitely comprehend the limits of their personal “scope of practice,” and in this way they will certainly come up with
remarks that are overbroad when conversing with clients.
It’s the equivalent occurrence; they have been minimally schooled in a subject, don’t understand the entire degree of the idea, and finally assume they are the Masters.
Oh my goodness! Incredible article dude! Thank you so much,
However I am going through problems with your RSS. I don’t
understand the reason why I am unable to join it. Is there anyone else getting similar RSS issues?
Anyone who knows the solution can you kindly respond?
Thanx!!
AA
WU
First, thank you for the info, and your perception.
I can enjoy this blogging site and most importantly
this text. At this point, Personally i think I misuse much too enough time on-line, analyzing rubbish, generally.
This is a refreshing change from what I’ve known. However, I feel that browsing other’s ideas is a valuable investiture of at any rate a
few of my regular measure of amount of time in my timetable.
It’s just like rummaging throughout the
junk heap to get the gold. Or sometimes, whatever example functions for you.
Still, sitting in front of the home pc is most likely
as harmful to you as cigarette smoking and deep-fried
chips.
Folk now really should discover even more concerning these topics,
though there seems to by no means end up being an adequate amount of
hours. Right after time for family and friends, and naturally doing business,
exactly what time frame is left over to devote to grasping these kinds of most vital teachings?
Being an individual employing post-partum massage, alongside
conceptualizing internet websites and taking care of a few nodes, free time is most certainly a commodity that we really
do not currently have adequate amounts of. Most likely, I will be capable of finding the
time period necessary to review this critical point more honestly.
Many thanks for giving this kind of special source.
This particular blog website is advantageous. Articles like
this are in most cases left out, if real truth be told.
Many times the highest quality of the Online world isn’t the very
most vastly pushed articles and other content, but yet rather everything otherwise, the not addressed people with websites
that are in reality significantly more suitable as compared with those of crucial legendary people today.
Now this is this sort of a blog site. My
partner and I will provide guidance, sometimes whenever not persuaded to
do it. First of all is, continue authoring.
Secondary is, maintain authoring repeatedly. Third is, use your own personal blogging to develop your personalised style.
I am not even a blog author per se; I am in fact a Perinatal Massage Therapist.
I practice Postpartum Massage Therapy, serving a lot of
women to recharge following having a newborn. It’s worthwhile work.
That is exactly what I dedicate nearly all of my energy
doing, though I have as well liked writing from the moment I was small.
I look over plenty of books and just about every year I read through a collection far
higher compared with myself. haha In the up coming twelve
months, it could be I should get really serious with regards
to blogging and site-building. The problem is, it still cannot just simply a thought; the function of creating and building routinely helps make a blog page build over some amount of time.
RZ
ZP
Families currently really need to gain knowledge of even more on the subject of these kinds of matters, even though there
will under no circumstances turn out to be a lot of precious time.
After available free time for friends and family, and surely doing work, just what free
time is left to devote to gaining knowledge of some of
these most beneficial instructions? As an individual doing post-natal massage, in addition to designing web pages and dealing with a wide
range of web servers, spare time is presently a commodity that I normally do not possess an adequate amount of.
With a little luck, I’ll be capable of finding the period of time necessary to study
this major matter more exhaustively. Thanks to you for supplying this
kind of prized web page.
I work as a chair Massage Event Director.
I regularly ponder the thing that got men and women started off writing a
blog. In conclusion, a blog writer has something substantive to express, irrespective of whether hilarious, insightful, educative, or in any other case enjoyable to a number of people.
I consider a great deal of bloggers aim to aid other folks.
Naturally, right now there are clearly those individuals driven to the Art of Blogging considering that it may
be lucrative and convert directly into a full-time
gig. I view a lot more information sites in a day as compared with any individual in my metropolis, I can easily ensure you.
I suppose that’s just about all too imprecise, in fact.
Each blog owner will start publishing for a diverse, unique reason. In my line
of employment, individuals reserve a Massage For Events just for both equally common (unique birthday, business
enterprise and corporate social gathering, etc.) causes, in addition to unique grounds for example a Spiritual objective.
For what reason do a certain amount of freelancers continue on, whereas most people throw in the towel?
It’s passion! An interest for crafting, a passion for helping or making individuals chuckle or specifically what I don’t know.
An eagerness for telling tales for quite a few, I am sure.
Living really does have symbolism, nevertheless we have to be effective at
comprehending it. I just think writing a blog may easily possibly even be a form of
Spiritual undertaking serving unseen crowds of people.
Certainly, there are weblogs created by Sears and Gimbles (opted for
bankrupted stores deliberately! mouahaha) but I really mean honest blogs and forums.
Individualized web logs. Small Business sites.
School websites. Nonprofit websites. Personal blogs with heart and soul.
I actually value your featuring this info for individuals trying to get
more info on matters similar to this. Your weblog was well-written and very well researched, and that is significantly appreciated.
I actually am often searching for original blogs to follow and search frequently.
What’s up, blog owner and followers! I totally have the
ability to mention the fact that I take delight
in the Online existence. The growth of the personalized blog website, with regard
to me personally, was almost the greatest improvement ever before!
I love perusing web blogs, and this blogging site is most definitely simply no exception to this rule.
I am employed as an Chair Event Massage Advisor. That leaves me with a
lot of spare time during the day to read through web blogs and such.
I would instead read a blog rather than news virtually any day.
What normal individuals have to express,
the things these people truly feel and believe, this counts to me very a great deal greater.
A blog is so private and really important since it will come right
from a person’s imagination and essential reflection of their particular own. Naturally, I don’t mean a blog
designed for a record-breaking conglomerate; which probably not a “blog” in the least!
Your blog has supplied me personally with simply the data that I required.
I have recently been carrying out analysis on this kind of
subject matter for a short time, and it has taken a long while to discover a weblog that
gives all of the facts which i will need. I will enjoy examining more blogs written by this author down the
road, and will start looking here initially
when I actually currently have a research project.
First of all, thank you for the information, and your point of view.
I can enjoy this blogging site and specifically
in this article. At this time, I feel I fritter away much too
much time on the web, perusing junk, actually.
This was a refreshing differ from that experience.
Still, I think that examining other people’s thoughts is
a valuable investment of at any rate a few of my regular allotment of time in my program.
It’s just like searching throughout the junk heap to discover the wheat.
Or maybe, whatever analogy is effective for you.
Still, sitting in front of the home computer is probably as bad for you as
cigarettes and fried potato chips.
ALRIGHT. The following would possibly not look to
be applicable, then again my own situation practical knowledge is valid.
I am a Prenatal Massage therapist. I consult different women of all ages almost every work day and aid them all to have a much
less stress filled, a lot more fulfilling, and much less
symptomatic pregnancy. Almost every distinct person presents with unique difficulties.
Massage by Mountainside On-Site Massage addresses this situation, but as a consultant I really need to always be adaptable and ready
to take a look at information on how to most beneficially help.
Generally there is no instance where a single resolution would likely aid anyone.
That is my very own situation, but my way of explaining might be unclear.
Lumbar pain will not be all which a mother-to-be is concerned with.
Similarly, no kind of folks ever suffer in a similar manner, and to assist these folks, therapists are required to
come to be exceptional listeners.
Individuals presently ought to discover even more concerning these kinds of subject areas, although there will by no means
turn out to be enough available free time. Right after precious time for friends and relations, and as expected working, exactly what
energy is left to expend to understanding these kinds of foremost teachings?
As an individual doing postnatal massage, coupled with making
online sites and working with a variety of web servers, available free time is without doubt a thing which i never already have sufficient quantiities of.
Ideally, I’ll be able to find the time essential to master this beneficial
concern more exhaustively. Many thanks for giving this unique fantastic website.
My own existence as being a bustling massage party planner doesn’t necessarily
leave a lot of precious time over for sharing my personal
views on websites, ordinarily. Yet, having already stopped by at this time, I
was trying to make sure that I began on the right footing, and start off with a
comment. It’s gladdening to me that there is a post about this significant issue,
because it isn’t spoken about nearly enough in pop culture.
I felt like your post was timely and relevant. Thank you very much for publishing
this specific trove of information; in the event that more people develope time-worthy and sensible musings, I gander
everybody’d just about all understand existence considerably better.
I, personally, think we should focus more on fitness and
diet regime and free from staying perpetually in front of the
computer or cell phone screen. Go outside and don’t
be cemented to the cellphone at the same time also,
and live life with appreciation for the natural world.
That helps people feel grounded. Philosophizing about key memes is likewise an experience that can help us know ourselves.
I actually sometimes encounter content such as this at quite possibly
the most improbable instances. For example
right now, I was researching a specific thing on an absolutely different concern and
I found this article in its place. I normally pass time hunting the internet for content,
but yet mainly I prefer to be outdoors, backpacking
in the local woods. Nothing at all is like outdoors to help you feel rejuvenated.
This is whenever i am not functioning as an in home
massages therapist. It uses up practically all of
my own energy.
This is definitely not the first moment in time I came upon a
weblog that was seriously worth writing comments on. This
exact document is beneficial to me. I certainly
live a lifetime of plenty of modes; that is to say that my work routines
vary. I practice Postpartum Massage. Furthermore, I buy and
sell collectibles. I’m being taught Linux. And, that’s just
a couple of pursuits I carry out. In any case, my idea is, each
one of us approaches a subject matter from a exclusive standpoint.
Every single experience all of us have forms us and our viewpoint
of our own selves and the environment all around us all.
I presume discovering some superior perception of real truth is just where all the things many of us speak
about brings us to. So, precisely how do people young and old afterward relate?
Is it even possible? I suppose our own personal contributions to the Internet Commons transforms
a large number of individuals, most likely.
So consider that, bloggers and commenters. Your own ideas may possibly assist some other person. Or even, commentary can easily do the reverse.
Plus, everyone, meaning, you, me, certainly have to care to attempt to help other individuals to mature and absolutely
not decline. Thank you for this blog site; I actually am filing this blog together with my various
other high-quality personal blogs I’ve come across that I sometimes troubled to
comment on. Keep writing! Always keep articulating!
What I truly concern myself most about is health themes.
By myself, I signed up for magazines oriented toward this subject, and I
preserve informed about the latest health studies.
Just how is this relevant? I feel there is no more significant focus for my
hours. Moreover, this site entry seems as though it’s worth my time to read.
I sift through thousands and thousands or more of blogs weekly.
Honestly, my back typically is uncomfortable
and I need a different diversion. lol Anyway, I think if absolutely everyone wrote about their
niche in the world, and did it articulately, we’d have a more interesting society.
I am constantly seeking for new info about this kind of significant topic, and
am specifically ecstatic the moment I actually locate sites which might be
well-written and well-researched. I want to thank you for offering
this good data, and I look ahead to read more out of the blog in the near future.
I am definitely seeking for new information upon this significant theme,
and was specifically happy when I actually find sites which might be well-written and well-researched.
Thank you for providing this good information, and I take a look forward to reading more right from your weblog in the
foreseeable future.
I am always seeking for new information upon this significant topic, and am especially delighted
when I find blogs which might be well-written and well-researched.
I want to thank you for providing this good data,
and i also glimpse forward to learning much more
from your blog page in the long run.
While I am scannnig the internet, usually I like to check out
writings I come across. Generally, I can also locate fresh
knowledge, or simply a contemporary means of seeing issues
that I hadn’t regarded previously. In such a situation, I’m definitely grateful.
Given that I haven’t got the opportunity to go online a lot,
owing to my job as an massage at home therapist, I make
an effort to capitalize on my experiences on line.
I have a job as an Event Massage Advisor. I forever ponder just what exactly got individuals started off blogging and site-building.
Eventually, a blogger has a little something significant to
talk about, no matter if very funny, unusual, enlightening, or in any other
case interesting to other people. I presume a whole lot of writers strive to
aid other folks. Of course, generally there are obviously those driven to the vocation given that it could quite possibly be highly profitable and develop straight into a fully committed career.
I check out a lot more weblogs in a year as compared
to anybody in my area, I will promise you. I think that’s just about all far too hazy, in reality.
Each and every writer starts publishing for a completely different, one-of-a-kind basis.
In my line of work, ladies book a chair Massage For Events just for both normal (special birthday,
business enterprise and corporate social gathering, etc.) motives, in addition to exclusive motives for instance a
Self-Development intent.
For what reason do several authors keep going, even while others stop
trying? It’s enthusiasm! An interest for composing, a passion for coaching or making us all giggle or whatever.
An enthusiasm for storytelling for a great number, I am positive.
Living really does have a message, but people have to be effective at becoming familiar with it.
I believe writing a blog could possibly be a kind of Spiritual undertaking
aiding silent and invisible masses of individuals. However, there are blogs put out by Sears
and Gimbles (selected defunct outlet stores intentionally!
hehe) but I just mean substantial websites.
Individual blogs. Small company blogs and forums. School
sites. Charity blogs and forums. Blogs and forums with spirit.
Exactly what I definitely concern myself most in regard to is
wellbeing subject areas. I sign up for catalogues dealing with
this subject, and I always keep up to date about the most recent wellness lab analyses.
In what way is this of any use? Personally,
I think there is no more significant focus for my energy.
Likewise, this web log seems as though it’s worth the
time to read. I dig through thousands or even more of websites every week.
Honestly, my spine typically is painful and I need a brand-new hobby.
lol Anyway, I believe if absolutely everyone composed blogs about their unique time in the
universe, and did so clearly, we’d experience a more interesting Planet Wide Web.
Guys and women at this time want to realize some more regarding
these kinds of subject areas, despite the fact that there will
in no way turn out to be a lot of spare time. Right after
time for relatives and buddies, and of course being employed, exactly what time frame is left to dedicate
to understanding any of these critical teachings? Being an individual doing post-partum massage
therapy, and also styling website pages and taking care of quite a few web servers, available free time is most certainly a commodity which i really do
not currently have enough of. Almost certainly, I am
capable of finding the energy important to review this essential article more in-depth.
Thanks to you for producing this unique valued web page.
In real life, virtually all questions are very much
more challenging than a random everyday observer may
determine, influenced by their past experiences.
I’m not announcing that am an authority on this subject at-hand, for that reason I want to guess it’s for some other online community members to think of.
I am certainly not wanting to make problem or be
bothersome. Instead, I recognize from experience that this can easily be the situation. I practice Postnatal Massage Therapy, and
in my own targeted concentration, I experience it considerably.
Just-graduated Postnatal Massage Practitioners are likely to overpromise;
which can be, these people don’t at this point absolutely recognise the limitations of
their own “scope of practice,” and in this way they will probably come up with statements that are overbroad
when conversing with clients. It’s the identical occurrence;
they have been brought in to a topic, don’t fully understand the comprehensive breadth of it,
and finally (wrongly) conclude they are the Masters.
I am definitely looking for brand spanking new data upon this kind of essential issue, and
was especially delighted when I actually discover
sites which have been well-written and well-researched.
Thanks for featuring this kind of superb data, and
i also glimpse ahead to learning much more from the blog page in the future.
Hi there, blog person and followers! I honestly could say the fact I am
keen on the Internet. The rise of the personalised
blogging site, with respect to me, was indeed almost the finest progress at any
time! I really like looking at web logs, and this valuable
blogging site is obviously certainly no different.
I work as an Chair Party Massage Manager. That leaves me personally having quite a lot of
spare time during the work day to read blog and such.
I would alternatively go through web logs as compared with media just about any time.
What regular folks have to speak about, the things these people feel and think, that is important
to me personally so a great deal more. A blogging site
is so unique and so heart-felt for the reason that it stems as a result of a individual’s creativity
and inner manifestation of themselves. Obviously, I
never really necessarily mean a blog designed for a massive conglomerate; that’s definitely not a “web log” at all!
My own day-to-day living as being a hard-working massage
party planner doesn’t leave a lot precious time over for contributing
my very own opinions on articles, in most cases. Nevertheless,
having visited this morning, I wanted to be sure I started out
on the best possible footing, and start out with a comment.
It is heartening in my opinion that there is an article on the subject of this key issue, since it is not tackled nearly adequately
in this culture.
We can easily almost all benefit from discover more regarding
our self and our health and health. Specified actions and movements amounts can easily currently have superb benefit
to us, and all of us have to study more info about them.
Your blog seems to have provided a valuable perspective that is to be helpful to various groups and individuals, and I just appreciate the
writing your experiences in this way.
Salutations, blog proprietor and viewers! I ultimately
might assert the fact that I definitely like the World wide
web. The increase of the individualized blogging
site, pertaining to myself, had been just about the most excellent
progress at any time! I love examining blogs, and this valuable
blogging site is really no exception. I work as an Chair Party Massage Planner.
That leaves me with plenty of leisure time during the work day to discover blogs
and forums and so on. I’d personally preferably read through a blog as compared
to news just about any day. What regular men and women have to express, exactly what they feel and think, that is important
to me very a great deal more. A blog site is so very personalized and considerably special for the reason that it comes from a person’s originality and interior reflection of their particular own. Not surprisingly, I don’t necessarily mean a internet site designed for a giant conglomerate; that’s definitely not a “blog” at all!
My own existence being a very busy massage party planner doesn’t
always leave very much spare time over for contributing
my opinions on websites, normally. On the other hand, having already visited today, I wanted to see to it that I started out on the right foot, and begin with a
comment. It’s gladdening if you ask me that there is a blog post to do with this key issue, because it isn’t spoken of nearly sufficiently in pop culture.
I really value your providing these details for individuals trying
to find out more about subject areas such as this. Your weblog was
well written and very well investigated, that is certainly considerably treasured.
I am constantly seeking fresh websites to keep in mind and
surf frequently.
We love your featuring this info for folks hoping to
know more about topics similar to this. Your blog page was well written and very well investigated, and that is very much treasured.
I am definitely seeking for new sites to follow and read frequently.
This particular blog website is enlightening.
Articles including this are constantly missed, if
fact be told. Normally the very best of the Internet has not been the most publicly
marketed written content, but yet rather everything else, the neglected public with
websites that are seriously a great deal better when compared to those of crucial
widley known people today. Now this is this type of a blog
page. My husband and I will certainly supply assistance,
quite possibly every time not made to do this. Very first is, continue to keep creating.
Secondary is, always keep penning constantly.
Thirdly is, implement your own personal blog website to improve your individualized presence.
I am certainly not a blog writer per se; I am honestly a Postnatal Massage Practitioner.
I practice Perinatal Massage Therapy, being able to help females to heal soon after having
a little one. It’s rewarding work. That is exactly what I
invest the majority of of my work time accomplishing,
though I have additionally adored producing from
the time frame I was youthful. I am a well-read individual and just about every
single year I read a bunch way taller compared to myself personally.
haha In the next four seasons, it could be I could get really serious
in relation to operating a blog. The matter is, it cannot simply just a good idea; the activity of composing and publishing continually will make a blog
page mature in time.
Folk at this time should really read and learn further regarding
these kinds of matters, even though there will never turn out
to be sufficient precious time. Following time for loved ones, and as expected holding a
job, precisely what energy is left over to commit to learning these kinds of essential theories?
Being an individual practising post-partum massage, in addition to designing sites and taking care of
various machines, precious time is unquestionably a commodity
that I usually tend not to have enough of. Quite possibly, I am able
to find the precious time essential to research this major content more honestly.
Thanks to you for supplying this particular significant website.
Women and men right this moment really should study somewhat more in relation to
these kinds of subjects, even though there will hardly ever be
more than enough time. Following precious time for family, and of course working hard, precisely what spare time is left over to
commit to learning each of these most important instructions?
As an individual applying post-natal massage, in addition to setting up sites and running multiple machines, free time
is definitely a commodity i will never possess sufficient amounts of.
With a little luck, I am capable of finding the energy needed to understand this critical article more thoroughly.
Many thanks for bestowing this specific important aid.
Americans now really have to understand a tad bit more
pertaining to these kinds of subjects, though there will never ever be a sufficient quantity of precious time.
Following time for friends and family, and needless to say working hard, precisely
what spare time is remaining to allocate to getting to know each of these crucial
teachings? As an individual practising postnatal massage, along with styling internet websites and working with a
few servers, precious time is obviously a commodity that I
tend not to currently have a sufficient quantity of. Quite possibly, I will be able to find the precious time required
to master this important and vital subject matter more exhaustively.
Thanks to you for dispensing this particular laudable
webpage.
This is definitely not the very first time I encountered a blog page that was
worth writing comments on. This particular write-up is
enjoyable to me personally. I ultimately live
a life of many ways; that is to say that my job
functions range the gamut. I practice Perinatal Massage Therapy.
On top of that, I buy and sell antiques. I’m gaining knowledge of Centos8.
And, that’s only a handful of pursuits I carry out. Anyhow,
my point is, each one of us all approaches a concern from
a one-of-a-kind point of view. Nearly every experience all of us have shapes us and our
own viewpoint of ourselves and the community all around us all.
I imagine trying to find some more significant meaning of actuality is where everything
all of us converse about brings us to. So, precisely how do
most people then relate? Is it even possible? I think that each of our very own contributions
to the Web Commons shifts a great many individuals, most likely.
And so think about that, bloggers and commenters. Your
individual brief review can potentially help out someone else.
Or even, responses can achieve the antipode.
Plus, you actually should care to try to benefit other individuals to
improve and certainly not wither. Thanks for this specific page; I
actually am submitting this blog together with my other first-rate personal blogs I’ve come across that
I actually troubled to opine on. Always keep penning! Keep revealing!
This excellent blog site is remarkable. Topics including this are more often than not
forgotten, if real truth be told. Often times the very best of the Web will not be
the very most vastly touted articles, though instead
all things else, the not considered herd with web logs that are in reality even better when compared to the ones of major influencers.
Now this is such a website. My partner and I are going to
lend hints and tips, sometimes whenever not motivated to do this.
First is, always keep crafting. Secondary is, always keep crafting on the
regular basis. Lastly is, start using your blog website to create
your own personal style. I am not a blog writer per se;
I am actually a Perinatal Massage Practitioner. I carry out Perinatal
Massage Therapy, helping ladies to get well after having a infant.
It’s pleasing activity. That is precisely what I
spend almost all of my hours undertaking, however
I have in addition appreciated posting from the time period I was little.
I am a well-read individual and each calendar year I read through a heap
way higher as compared with myself personally.
lol In the next four seasons, it could be I should get serious concerning blogging and site-building.
The matter is, it cannot solely an understanding; the labor of composing and
submitting constantly tends to make a blogging site grow over some amount of time.
I have a job as an Event Massage Advisor. I usually just think
about what precisely got people started operating a blog.
Finally, a blogger has a little something meaningful to share, irrespective of whether fun, informative, helpful, or in any other case compelling to many people.
I suppose quite a lot of blog writers want to support other
folks. Surely, there are undeniably those individuals
attracted to the Art of Blogging mainly because it can be money-making and turn into a full-time career.
I view a lot more blogs in a month when compared with anybody in my town, I could reassure you.
I suppose that’s just about all too obscure, really. Each and every writer starts off posting for a diverse, completely unique
motive. In my line of employment, individuals organise a chair Massage Event just for both equally basic
(unique birthday, corporate and business get together, etc.)
factors, as well as particular factors for instance a Spiritual purpose.
Precisely why do a handful of freelancers carry on, whilst others toss in the towel?
It’s enthusiasm! A passion for creating, a passion for helping or helping
to make all of us have fun or whatever. An excitement for storytelling for a great number, I am certain. Life
does indeed have meaning, however all of us have to be effective at becoming familiar with
it. I think operating a blog can sometimes be a variety of Self-Discovery “job” being
able to help unseen masses of men and women. However, there
are web logs published by K-mart and Gimbles (selected
defunct merchants deliberately! haha) however I mean genuine sites.
Personalized blogs. Mom and Pop Business blogs and forums.
School web logs. Local Association blogs. Personal blogs
with love.
People may all gain from learning even more about ourself and our overall health and
wellbeing. A number of actions and movements levels can have wonderful profit to us, and we all ought
to study more information about them. Your weblog has
furnished a worthwhile viewpoint that is useful to a number of communities and individuals,
and I just love the showing your emotions in this manner.
I have often pondered exactly how urgent it actually
is to understand our own limitations, and also our own talents.
When you’re undertaking precisely what you happen to be greatest at, you are
somewhat more in touch with your true self. When you find yourself engaging in something which happens to be stressful, quite often you like it because it is an obstacle.
I have observed that there are a few people eager to explore their very
own skills, even while you find other individuals whose life predicaments have made such not possible.
Almost all people simultaneously would not realize that massage for kids may also help small children whose households
are afflicted with adversity. Fighting worry or nervousness in little children creates lowered stress symptoms for the mothers and fathers, who will afterward live their own everyday life in an increasingly laid back manner, maybe taking improved care of themselves
as well.
Yes, after following the instructions I was able to download the data from Power BI dataset.
Thanks.
I was facing a similar issue in Excel while loading a table in my model using Power Pivot.
The issue arises, when our data header contains an excel reserved word. In my case, there was a column name ‘catalog_ name’ which is a reserved word for excel. I changed the column name to ‘catalog_name_1’ and the error was gone.
Bonjour Matt,
Je vous dis infiniment merci pour votre post qui vient sauver ma détresse face à ce genre de dysfonctionnement que j’ai rencontré avec ces outils tout de même puissants.
J’espère seulement que les autres dans cette même difficulté sois aussi chanceux comme moi pour retrouver votre post à ce sujet.
C’est généreux de votre part
Well! E_ACCESSDENIED which code error is 0x80020009. While working we find 12 different kinds of HRESULT Errors which code and nature of the error is different. You can see the list at https://procpedia.com/hresult-values and this page also contain the possible solution of each hresult-values. Hope it will also help you to get the solution of your problem.
Hello I have a similar problem and I think this is what happens to me as well. I have a powerpivot that pulls data from a text file. I update the text file very week for new data. Now when i refresh it loads and keeps saying its retrieving rows until it reaches row 950,000 and it says after “error.
It says: “We couldn’t refresh the connection. Please go to existing connections and verify they connect to the file or server.”.
I tried to delete the connection and start all over. And it worked , I was able to import the data all over but that means losing all my calculated columns, calculated measures and need to update all the tables in the pivot tables. This is hours of work.
I have the connection greyed out and also have the message at the bottom.
Is there a way I can edit the current connection or at least replace it with the new one so i don’t lose the calculated measures,columns, and won’t have to fix all the pivot tables i have to pull the right table?
Any help is appreciated. I am working with a huge file that will take hours to update every pivot . FYI i tried to pull this text file in a separate excel file. And it worked. But in this powerpivot file it doesn’t let me.
I am not sure this is the same issue. Can you please post your question at http://powerpivotforum.com.au as is is a better place to seek help
I tried what you said, but it didn’t work for me * sob *
I just got the same error Load to Data Model Failed
Are you using 2010?
Hello all,
I thought I post a real solution, I did not see it in the comments above. I have had this happen to me a handful of times and had to rebuild my reports due to this BIG issue with Power Query. I had the same issue again today, due to me using PQ to remove columns I “thought’ I didn’t need on my report, to realize I needed one of them.
I tried everything I have learned so far and nothing worked until I remembered this one trick that worked a while back and solved this issue.
What you do is after you modify your PQ (Power Query Editor), say you delete one of the Applied Steps, you get this message saying that you will in fact break your connection to the Data Model; that is OK. Do it and save and load.
On the Power Query Tab, in Excel, click “Show Pane” under Workbook Queries. You will see a yellow triangle with a message that it could not load data, or something to that effect. Don’t panic. I did. Right click on that connection and select “Load to…”; once there find the “Add this data to the Data Model” and UNTICK it and click “Load”. Wait for it to change to Connection Only. Right click on that connection and select “Load to…”; once there find the “Add this data to the Data Model” and TICK it and click “Load”. Once you do this it should start reloading the data to the Data Model and you should be good to go. The only issue with this is that your PowerPivots lose the “Values” within them so you will have to remember how your selections were done.
I hope this helps you and know that I have done this a few times with 100% success.
Good luck!
Christian D.
Hi Matt, thanks for the really clear and helpful blog post.
I’m using Excel 2013 (O365 click to run so I think up to date) where I thought this issue was fixed i.e. that you can’t modify the column names in PowerPivot, you have to do it through the PowerQuery window (the UI prevents it – as per Ken Puls’ post http://www.excelguru.ca/blog/2015/10/21/breaking-power-query-via-power-pivot-is-a-thing-of-the-past/#comment-261235).
I’ve recently renamed a couple of columns in PowerQuery, and seem to have triggered the dreaded HRESULT 0x800A03EC error (although I can’t be absolutely sure this is what caused it).
None of the 7 connection strings are greyed out. If I try to right-click refresh on the Workbook Queries pane, it says then says “Download Failed” (little yellow warning symbol) and allows me to choose Load to “Connection Only” / “Data Model” once again. This seems to create duplicate copies of all my tables in PowerPivot For Excel window.
Have you any experience of this in Excel 2013 since the don’t-edit-me-in-powerpivot-edit-me-in-powerquery issue was fixed? I’m not sure how to avoid the issue especially if I haven’t ended up with the greyed out connection string and didn’t edit anything in PowerPivot… many thanks
I am also using the o365 version and definitely have the warning message “cannot edit, go to Power Query to Edit”. I have not had any issues since this change (ie zero). Now of course that is a sample of 1, however I have also never heard from anyone that they still have issues – until now.
Sorry, I don’t have any other insights
Thanks Matt.
It’s good to know it’s a likely an exception rather than the norm. I spent a while trawling through all the reports of previous misery thinking oh no I can’t use this buggy mess, until I realised that it had been (generally) fixed. It’s great that everything is moving so fast, but it means you have to have your wits about you when you are reading blog posts!
The whole model crashed out and hung the PC about two weeks ago so I think there may be a glitch in the matrix. The fact the connection strings were still editable certainly makes it seem like a slightly different issue.
I have an earlier back up so I am testing adding new columns and renaming those, rather than renaming the existing columns. Hopefully that’ll limp me through until I can commit the whole darn thing to PBI desktop instead. Oh how I wish PIBD had pivot tables too… spoiled for choice I suppose.
Take care.
Hi Matt,
Hallelujah Hallelujah Hallelujah
Yesterday I was the most happiest man in the world …… simply because of you 🙂
It is almost three months trying to build my tables to compute the cost of manufactured goods for various products, and this requires to furnish many individual tables and link them together to reach the cost for each product (this includes imported materials, custom duties, transportation, banks, salaries, sales,…. etc.) and finally to reach my Income Statement.
I prepared 28 Excel sheets and tables, pulled them to PQ, and then to PV. during these period I need to restructure my PQ files and to refresh it in my PV tables but I started to face the very silly night mere message (Exception from HRESULT: 0x80020009 (DISP_E_EXCEPTION)).
Yesterday while Googling the error, I have read your fantastic solution and I started to carry it out your idea and started to create not only one table but various parallel tables to the original tables and according to my DAX formula. I took my time to extract formulas from the original tables and paste it to the new helper tables and it worked perfectly.
Thanks Matt for your great great help …. Bless you.
Lotfy Kozman
? glad it worked for you
Thanks a lot for these informations!
It seems like this bug has been fixed with Excel 2016. If anyone can confirm, we can use the new power query without the workaround.
Hey Shawn,
Indeed, Excel 2016 was designed to remove this bug, and prevent you from doing things that would accidentally break the ability to refresh Power Queries. Specific actions that are prohibited are renaming columns or tables, and deleting columns sourced from Power Query.
The even better news is that this is also now fixed in 2013 as well. I actually just published a post on this a few minutes ago, which you can pick up here: http://www.excelguru.ca/blog/2015/10/21/breaking-power-query-via-power-pivot-is-a-thing-of-the-past/
Hey Ken. I noticed that I get the “can’t edit this table because it was created in Power Query” message now, but I hadn’t made the connection that MS had actually “fixed it”. Thanks for sharing
Ken / Matt,
Do you know if there’s a backwards compatibility issue between Excel 2016 and 2013. I used Excel 2016 Get & Transform to create a query hat feeds into Power Pivot. When I try to run this in Excel 2013 however the data load fails with this warning message
Cannot connect to the data source for table “CGRawData”. Make sure that Data Source is available. If you contact Microsoft support about this error, provide the following message:
Failed to connect to the server. Reason: The connection could not be refreshed. This problem could occur if the connection was copied from another workbook or the workbook was created in a newer version of Excel.
CGRawData is my Table/Query name
Up until now I was not aware of any issues. But it certainly sounds like a backwards compatibility issue. You could try to cut and paste the power query code into a clean excel 2013 workbook and see where it fails. Maybe there is a compatible transformation you can use instead of the failed item. Just a thought.
It’s interesting as the Microsoft Power Query team got back to me today and suggested installing the fixes that were used to address this original issue.
“Make sure you update and install KB3039739, KB3085502 and KB3039800 to see the fix working”
However I tried out my 2016 file on my old 2013 machine that had these fixes installed and I still got the same problem.
Unloading the query from Power Pivot while in Excel 2013 and then re-loading it worked fine, but then we’re back to the old issue of losing reports, measures etc…
Clearly this is a pain as most of our clients are on Excel 2013
I’ve replied to them with as much detail as possible and will keep you posted on what I hear back.
Solved.
There is a patch for this, however a super simple workaround is to just save the file while in Excel 2013 before refreshing the query
https://support.microsoft.com/en-us/kb/3101561
Matt,
I think there is another warning that is appropriate:
If one creates an Excel table and uses it as a source with Power Query, do not make changes to column names in Excel afterwards; make your changes in Power Query instead.
May apply to any table used as a source to Power Query.
That’s interesting. I have never had a problem here. I have just tried to add a new column in Excel for a table already connected to Power Pivot via Power Query and it works fine. What issues have you had?
Thank you so much for sharing !!!!
You save my morning and mental wellness
Thank you
Hey there Matt,
I read your blog post when you posted it.
And today I was trying to refresh a CSV file which is used within Power Query. Initially I had the CSV file in a different location. So I went into Power Query, and changed the Source to look at the new location. One I did that, it all looked good within Power Query.
I then clicked on Close and Load, and it will not load the updated data into Power Pivot. I have not made any changes to my Power Pivot window, or table or anything.
I am going to assume that if I make a change to the Power Query Source, that this modified the connection and makes it read only?
I even tried creating the folder structure so that when I open my Excel File and try and click on refresh, it will find the CSV file where it thinks it should be. But that still fails.
Any idea’s?
Thanks
Gilbert
well this does sound like the same issue. You cannot change anything once it is read only, including the file location. I had the same issue. Follow my instructions above to see if the connection string is greyed out. If it is greyed out, it is read only and you cannot change anything. Assuming this is the problem, you either need to put the file back in the original location, or remove the table from Power Pivot and then reimport it. You will lose any connections you have and any measures inside the table. You can
1.manually move the measures to a new table,
2. remove the table
3. reimport
4. move the measures back into the table
5. reconnect the tables.
It is a pain, but given you now know what the issue is, this should be the last time you have to do this 🙂
I’ve just been trying to set things up in Excel 2010 for a client (Power Query manipulating the data and then pulling it into PowerPivot via existing connections).
I keep hitting the issue of the connection breaking as soon as I save the file. Is there any way around this other than copying and pasting the existing connection string each time?
The following should work.
1. Create the Power Query, click Close and Load To, and select “create connection only”.
2. Go into the Power Pivot Window, go to the Design TabExisting Connections and select the Power Query connection you created.
Everything should work from there as a normal connection without issues.
Is this how you are doing it?
Hey Matt –
Thanks for the insightful post! I’m running into am issue that honestly I am completely lost wIth. I feel like I’ve tried everything short of recreating my workbook from scratch. I’m using this as the last resort because my model includes some 30 queries or so.
I’ll try and breakdown the issue as best I can. Essentially, I created a series of functions that wrap two parameters into some teradata sql in the where statement. I then use an excel parameter table with two columns as the two parameter inputs, call the function to have my modified query. This all works fine and loads into power pivot successfully. The problem comes in when I save excel. The moment I save excel all of the power query strings change for no reason I can see. I have made no modifications to the power queries so I have no idea why they are changing. The error I’m getting indicates that it may be related to creating the query in a newer version of excel.
One thing to note is I created all the queries except the queries with the embedded functions in power query 2.18 and the embedded function queries in power query 2.22. Would the queries created in the earlier version be causing this issue? Is my only answer to recreate the model in a fresh workbook? Any help would be greatly appreciated!
Thanks
Kellan
Hi Kellan
This is a pretty technical scenario so it is hard to know where the failure is – but then again I guess you already know that right?!
I have seen no evidence that a newer version of Power Query has any problems with queries created in older versions. Of course that doesn’t mean it can’t happen. Given the number of updates that have come out, I think MS would be all over this.
Have you tried contacting the MS team with the frown button in Power Query? I have found them to be very responsive.
Hey Matt –
Thanks for the response! You’re right, it was definitely a technical scenario lol!
So I wanted to wait until the MS team released their newest update to see if it was just a minor bug. And it was! It turns out that there are scenarios where every time you save the workbook all of the Power Query connection strings change. The newest release yesterday fixed this issue and I’m off and running!
Excited to be back to building 🙂
Cheers,
Kellan
Good timing. Had this problem this morning.
To not change Power PivotOrQuery after a Power Query2Pivot kind of takes the beef away.
I hope Microsoft can solve the problem before it becomes the published norm (in books).
Ola, just to clarify. It is 100% ok to change Power Query all you want. Just make sure you always make any changes you need in Power Query and never in Power Pivot. That way you will get everything you need and won’t have any issues.
What about creating a column to get data from other table using the Related()?
Calculated columns are fine, just don’t change any existing columns.
Hello, I appreciate your guidance! However, I received the error “Load To Model Failed” and all I did was REFRESH the table within power query- I made no structural changes to the table, but the table needed refreshing because it went from 42 rows to 50 rows of data… would this matter?
Well if it were me I would open the power query, go to the first step, refresh the preview, then work out where the issue is from there
Hi,
Can somebody help me with this error
SELECT [‘2016$’].* FROM [‘2016$’]
UNION ALL
SELECT * FROM ‘C:\Users\saysfv\Documents\2017\2017-test.xlsx’.[‘2017$’]
The SQL statement is not valid. There are no columns detected in the statement.