cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zimmermanaric
Helper I
Helper I

Values Change in Append

I am wondering if anyone has come across this before. We have 4 seperate databases that are all setup with the same table structure. I am pulling from the data for general ledger transactions from each of them as seperate queries for the past two years. When they are seperate, the summed value for all of the income statement accounts are rock solid. Each refresh shows the same answer. When I append all 4 of the queries together as a new table, each time I refresh I get a different result in the combined table for periods in the past that are closed.

 

Anyone have that issue before?

1 ACCEPTED SOLUTION

My apologies for the delay in response here. I was on vacation when you asked the question and the email was lost in my inbox until now. I did solve this purely by luck. It appears that if you have conflicting privacy levels (and sometimes even not) the append will give different results. I turned off the following option in my reports and things stablized:

 

File=> Options and Settings => Options=> Privacy

 

From there I chose the other radio button option "Ignore the Privacy Levels and potentially improve performance".

View solution in original post

21 REPLIES 21
dgbraqe
Frequent Visitor

Hello,
Did you open a ticket at Microsoft? Got any returns? I'm having the same problem, using the "append as new" option, the new table is different from the original tables.

My apologies for the delay in response here. I was on vacation when you asked the question and the email was lost in my inbox until now. I did solve this purely by luck. It appears that if you have conflicting privacy levels (and sometimes even not) the append will give different results. I turned off the following option in my reports and things stablized:

 

File=> Options and Settings => Options=> Privacy

 

From there I chose the other radio button option "Ignore the Privacy Levels and potentially improve performance".

View solution in original post

dgbraqe
Frequent Visitor

Hello,
Did you open a ticket at Microsoft? Got any returns? I'm having the same problem, using the "append as new" option, the new table is different from the original tables.

MFelix
Super User III
Super User III

Hi @zimmermanaric,

 

What do you mean by having different values for periods past that are closed?

 

How are you appending the tables and how are you calculating the result for the months that are closed?

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi! I am pulling the data into Power BI via Sql. There are only a few columns:

 

1. period

2. year

3. amount

 

The where clause in the sql statement is limiting it to year 2017 and higher. Then I am using the "Append as New" function in the Advanced Editor. Once done, I create a simple matrix table showing the value as amount, column as year and row as period. Each time I refresh, I get a different answer. The only account period that we have open is Feburary 2018. Prior Month's should be the same. This is only happening with a table that is appended from the 4 other tables. If I do the same matrix with each individual table, the numbers to not change on each refresh of the data.

 

Hi @zimmermanaric,

 

Sorry for the question but Why are you making new appends with several SQL and not a single SQL for everydata?

 

could it be possible that you are repeating data everytime you add a new query?

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



I really appreciate your thoughts! It is 4 sql statements that are written the same way, pulling the same columns and years, but connected to 4 differnent databases.

@zimmermanaric

 

Did you figure out why this is happening? I'm having the same issue.

 

I have two queries being appended into one. Each time I refresh the values from the appended table change, even though the values in the original two queries stays the same.

No I have not. I think I am going to have to open a ticket with Microsoft to help with this issue.

@zimmermanaric

 

I actually just fixed mine. In my original queries, I added an order by clause at the end. I don't know why this fixed it, but it did!

 

ORDER BY [TABLE_NAME].[COLUMN_NAME] ASC

 

Maybe this will fix yours as well? Good luck!

I really appreciate the update. Unforuatnely, I don't have an order by clause in mine.

@zimmermanaric

 

Couldn't you just add one to the end of your SQL query? Just order by year or period maybe?

I am very sorry. In my haste I misread your post. I thought you said that you removed your order by. I will try it and let you know.

@zimmermanaric

 

No worries! Hope it works for you .

That reduced the magnitude of the change in the number, but it will isn't adding up correctly and changes every time I hit refresh.

 

Hmm did you add it to all 4 queries you’re appending?

Yes. I added the same order by to all 4 queries. How many columns are you selecting in yours? Did you order by all of them?

 

 

Hi @zimmermanaric,

 

First of all let me apologize for not responding on this message but add some issues witht work, and also lost the track to the treadhs I had open on my Inbox.

 

Can you please give some more details on your setup? To my experience when I add several SQL in PBI the only issues I experienced similar was when people where inserting data in the database so every time I refresh during my tests the data was changing.

 

Regards,

MFelix 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi! Inserting records is not the issue here. I am querying information in the past that should not be changing.

 

 

I'm with the same problem. Is there anything new about this?

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.