Merged Field From Power Query Not Populating on Report Builder Side

[JJ51]

Hey Everyone!

I have an interesting issue.  I have a few dimensional tables that I am joining to a fact table because of a unique key based on the join. I am concatenating customer, sale type, product type, and product name because different combinations of those bill at different rates.  I then create a relationship between my final table and a billing table (just and excel sheet with the concatenation and billing rates). Everything works fine in Power Query, and in the report builder view at first... But after a service refresh some of those fields are empty. 

I downloaded the post-refresh .PBIX to see if I can identify the failure point.  It looks like the data is fine in Power Query, but in the report builder it is empty.  The field type hasn't changed; I already tried that one (thanks Guy in a Cube!).  After a refresh in desktop, the report builder view populates the field...  I feel like there is some sort of indexing issue or similar problem when it refreshes in the service.  The data source is a SQL database, one primary fact table and a handful of dimensional tables. 

Any guidance on either restructuring my data to accomodate the concatenation without joining tables or troubleshooting would be GREATLY appreciated.  This report has been driving me up a wall lately! 🙃

Thanks!

JJ

[JJ51 · Accepted solution]

I ended up having to join the tables as part of my SQL query instead of after pulling into Power Query

[confecielito]

I had to do the same, it seems that the issue is related to the way Power BI process the merge. I tried several options and first I ruled out that the issue did not have to do with the database and the data I was using (SQL Server) when the SQL query worked perfectly in an external editor.

[Anonymous]

Hi,@JJ51 . Thank you for your reply.
Table joins are performed in SQL queries, which is really an effective solution because it ensures that the data is properly joined before it enters the Power Query.
It's great to see you sharing the alternatives you use in the forums.
You can mark the alternatives you currently use as a solution,it will help more people find effective help quickly.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian

[JJ51]

Apologies for any terms that aren't standard!  I called Report View in PBI Desktop, "Report Builder", because I didn't have a better term for it 😁.

Here's a more clear process:

1. I used PBI Desktop to create the report. During this process in Power Query, I merged a few tables to get a concatenated unique key for a relationship with a static pricing table. 

2. I published the report to a workspace in Power BI Service, and then published the workspace as an App.

3.  Intially everything worked correctly in the app/Power BI Service.  However, after the first refresh a few of the merged fields came back null, which broke the concatenation; thus breaking the relationship between my tables. 

4.  I thought it was an error where boolean values can sometimes change to 0 and 1, but that was not it. 

5. I decided to download the file from Power BI service to see if I could better troubleshoot in Desktop.   Upon initial download the fields were broken, but with no explanation.  I then went to Power Query, and the fields were populated.  So, I went back to report view, refreshed, and voila! everything was working correctly again. 

That is where I am currently stuck.  There is something done by the refresh in the Power BI Service that differs from the Power Query/Power BI Desktop Report View refresh that is causing this. 

I've responded to your points below in bold. 

1. In Power Query and Report Builder, everything works fine initially.
Here in Report Builder is normal, here Report Builder refers to what, refers to Power BI desktop in the report view view mode, or refers to the production of paged reports using the report builder software, I personally prefer the former!  Report View where you create your pages of visualizations. 
2. However, after the service refresh, some fields become empty.
Here the service refresh refers to what, is that you these report production work are in the power BI service to complete is it, that is, you refresh is power bi service Correct, I am referring to the Power BI service scheduled refresh. 

3. you refreshed the service after the problem, and then downloaded the pbix file and use Power BI Desktop to open it, at this time in the Power query everything is normal, but in the Report Builder (that is, I think the report view view mode) in the empty, at this time the problem still exists. Correct. 
4. You then clicked Refresh in Desktop, the problem is solved, Report Builder view (Report View view mode) in the field will be filled correctly, there is no longer appear before the refresh of the field data appears empty values! Correct. 

To summarize, my guess is that you created the connection to the data source, the relational model and produced a report on power BI Service and subsequently downloaded it as a pbix file and opened it locally in Power BI Desktop.  I actually built this in Desktop and then published.  But downloaded from Power BI Service after I realized it was broken. 

I think it's really strange that this is happening. power BI Service acts as a Microsoft managed cloud reporting server where we can use some of the cloud services in it (including power query online)
You can see the Power Query service (including Power BI service) in each platform through the following links.
URL:The Power Query user interface - Power Query | Microsoft Learn

It is important to note that
The data preview in Power Query is only a snapshot of the data displayed, the real data loading and applying will only take effect after clicking the “Apply” button. Therefore, just because the data you see in Power Query is normal does not necessarily mean that the data you load into the report will be normal, the data you see in the Report View will be the main criterion.

This is also the reason why you mentioned: why the data processing in Power Query shows normal, but some of the data will be empty after loading into Power BI desktop.

In fact, this is a very confusing fact, and many users have similar thoughts as you: why I have successfully processed the data correctly in Power Query, but have problems loading it in Desktop.

This is because the actions you perform in Power Query without clicking the final Aplly button are previews and the actual data has not yet been processed successfully.  I applied all of my changes, saved locally, and published to a workspace. 

Below are my suggestions on how you can solve the problem:

You can save the .PBIX file downloaded from Power BI Service as a new file in Power BI Desktop and republish it to Power BI Service. Ensure that the data gateway is configured so that the server side can access the data source correctly. This method can help resolve issues due to the server-side refresh mechanism.  It is already cnfigured, there are no errors on the service side (I use this SQL server often).  I am wondering if this is more of an indexing/timing issue.  What I mean by that is that that the tables are either loading simultaneously or in the wrong order, and the merge step is looking for data that isn't yet populated.   Is it possible to choose the order in which tables load?  

[Anonymous]

Hi,@JJ51 .Thank you for your reply.

Thank you for answering my query in detail.
In fact, I discussed the ISSUE you encountered with other members of the team. We find it rather strange. Because Power BI Service, being a Microsoft managed reporting cloud server, does not normally have such issues.
Here are my answers and suggestions to your query.
For your query:
Is it possible for Power Query to select the order in which the tables are processed after the data has been processed (preview), when the data is being loaded.
Unfortunately, the data processing operations are left to the system engine to perform and the user cannot specify the order in which the data is processed and loaded artificially.

Here are my suggestions
1. You mentioned that the data source you are using is SQL Server (you mentioned that it is a SQL database)
So the data connection should be Import or Power Query.
Check if there is a real problem with the data you are loading into the report model.
If it is import connection mode, you can use XMLA (you need to have PPU or Premium license and enable XMLA read/write privileges in Admin portal) to achieve remote connection in SSMS.
To remotely connect to the semantic model of the reports in workspace in SSMS (since the Power BI report model is actually an AS model, you can process and view it as an AS model)
See if there are any problems with the actual data in the report model (the data in the import connection model is stored in the model as a data cache and can therefore be queried)
like this:

URL:
Semantic model connectivity and management with the XMLA endpoint in Power BI - Power BI | Microsoft...
 

XMLA remote connection workspcae using SSMS (SQL Server management studio):

Just like using DAX view on Desktop, you can now check what data is actually stored in a semanticmodel published to a service by using the xmla implementation.
To narrow down the scope of the check
Checking pbix files in desktop

Check the data in the semantic model published to the service in ssms
See if there are really any missing data in each table

2. For Direct Query connection mode, in this mode. In this mode, all the data acquisition is handed over to the data source for processing, and power bi only defines the data model and stores some metadata, so you need to check whether there is any problem with the data in your data source (SQL database).

3. check whether the account you use on Desktop and Service is the same Power BI account, to avoid that the account on Power BI service does not have access to the data source (I don't think the problem is here, but it is still necessary to check)

4. Please check if it's a gateway issue, you mentioned that there is no problem when uploading to service, but after performing the first refresh (I think when scheduled refresh) there is a problem, you can try to configure a new gateway data connection for this SQL data source and set the correct data source credentials. Make sure the connection is working
5. Please try to click refresh now in the semantic model to do a manual refresh (because there is a slight difference between scheduled refresh and manual refresh in the power bi service, you need to exclude this possible influencing factor)
Please try to clear your browser cache before execution to avoid interference.

6. If you can, please re-download the problem pbix file and check if the steps handled in Power Query are correct and if the Relationship in Desktop is correct. And republish it to the service and use the reconfigured gateway connection to connect to the report.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,@JJ51 . I am glad to help you.

Based on your description, here are my guesses about the problem you are experiencing and your operation.
In fact I am puzzled by some of the terms in your description, so please check if my guesses are correct and correct them promptly.

1. In Power Query and Report Builder, everything works fine initially.
Here in Report Builder is normal, here Report Builder refers to what, refers to Power BI desktop in the report view view mode, or refers to the production of paged reports using the report builder software, I personally prefer the former!
2. However, after the service refresh, some fields become empty.
Here the service refresh refers to what, is that you these report production work are in the power BI service to complete is it, that is, you refresh is power bi service

3. you refreshed the service after the problem, and then downloaded the pbix file and use Power BI Desktop to open it, at this time in the Power query everything is normal, but in the Report Builder (that is, I think the report view view mode) in the empty, at this time the problem still exists.
4. You then clicked Refresh in Desktop, the problem is solved, Report Builder view (Report View view mode) in the field will be filled correctly, there is no longer appear before the refresh of the field data appears empty values!
To summarize, my guess is that you created the connection to the data source, the relational model and produced a report on power BI Service and subsequently downloaded it as a pbix file and opened it locally in Power BI Desktop.

I think it's really strange that this is happening. power BI Service acts as a Microsoft managed cloud reporting server where we can use some of the cloud services in it (including power query online)
You can see the Power Query service (including Power BI service) in each platform through the following links.
URL:The Power Query user interface - Power Query | Microsoft Learn

It is important to note that
The data preview in Power Query is only a snapshot of the data displayed, the real data loading and applying will only take effect after clicking the “Apply” button. Therefore, just because the data you see in Power Query is normal does not necessarily mean that the data you load into the report will be normal, the data you see in the Report View will be the main criterion.
This is also the reason why you mentioned: why the data processing in Power Query shows normal, but some of the data will be empty after loading into Power BI desktop.
In fact, this is a very confusing fact, and many users have similar thoughts as you: why I have successfully processed the data correctly in Power Query, but have problems loading it in Desktop.
This is because the actions you perform in Power Query without clicking the final Apply button are previews and the actual data has not yet been processed successfully.

Below are my suggestions on how you can solve the problem:

You can save the .PBIX file downloaded from Power BI Service as a new file in Power BI Desktop and republish it to Power BI Service. Ensure that the data gateway is configured so that the server side can access the data source correctly. This method can help resolve issues due to the server-side refresh mechanism.
URL:
Query overview in Power BI Desktop - Power BI | Microsoft Learn
Manage Data Preview (Power Query) - Microsoft Support
 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.