Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
plew
Frequent Visitor

Appending data from external semantic model

I need to append data from 2 sources:

  1. Excel file data imported with PowerQuery
  2. Data from an external semantic model from a published PowerBI report. When I connect to this one, it only exists as a live connection in my PowerBI file, not as a Query though.
    plew_4-1715848923885.png

 

The option to append tables is only available in the PowerQuery editor. But the data coming from the conection to external semantic model is not visible there. 
How can I append these two?

 

View in the main PowerBI window:

plew_3-1715848744673.png

 

View in the "Transform Data" editor (data from external semantic model not visible):

plew_1-1715848561850.png

 

Append wizard view (data from external semantic model not available):

plew_2-1715848603253.png

 

 

2 ACCEPTED SOLUTIONS
v-xinruzhu-msft
Community Support
Community Support

Hi,

Thanks for the solution @lbendlin  provided, and i want to offer some more information for user to refer to.

hello @plew ,  the power bi dataset model cannot be edited in Power Query,you can't change data types of columns that are loaded from a Power BI semantic model or Analysis Services source. If you need to change the data type, either change it in the source or use a calculated column. You can refer to the following link about the limtations.

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models#working-with-a-c...

if you want to transform the data, it is better that download the pbix file, then get the data from the downloaded file, then import the data to the current power query, you can refer to the following link about it.

Download a report from the Power BI service to Power BI Desktop - Power BI | Microsoft Learn

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

plew
Frequent Visitor

This article also states explicitely:


The following Live Connect (multidimensional) sources can't be used with composite models:

  • SAP HANA
  • SAP Business Warehouse
  • SQL Server Analysis Services
  • Power BI semantic models
  • Azure Analysis Services

When you connect to these multidimensional sources by using DirectQuery, you can't connect to another DirectQuery source or combine it with imported data.

View solution in original post

5 REPLIES 5
plew
Frequent Visitor

This article also states explicitely:


The following Live Connect (multidimensional) sources can't be used with composite models:

  • SAP HANA
  • SAP Business Warehouse
  • SQL Server Analysis Services
  • Power BI semantic models
  • Azure Analysis Services

When you connect to these multidimensional sources by using DirectQuery, you can't connect to another DirectQuery source or combine it with imported data.

v-xinruzhu-msft
Community Support
Community Support

Hi,

Thanks for the solution @lbendlin  provided, and i want to offer some more information for user to refer to.

hello @plew ,  the power bi dataset model cannot be edited in Power Query,you can't change data types of columns that are loaded from a Power BI semantic model or Analysis Services source. If you need to change the data type, either change it in the source or use a calculated column. You can refer to the following link about the limtations.

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models#working-with-a-c...

if you want to transform the data, it is better that download the pbix file, then get the data from the downloaded file, then import the data to the current power query, you can refer to the following link about it.

Download a report from the Power BI service to Power BI Desktop - Power BI | Microsoft Learn

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

lbendlin
Super User
Super User

In DAX you can use UNION instead but that won't work with live connection sources.

 

What is the business problem you are trying to solve?

plew
Frequent Visitor

I need to summarize and analzye spend data, that is coming from two sources:

  1. One data set is in another PowerBI
  2. Second data set I need to download from another system, through an Excel export file (no possibility to get data directly from the source)

Both data files have different structure and there can be some duplicates between them. I have ideas how to clean them up in PowerQuery. But I'm stuck on the first step - getting the data together in one table to compare, wrangle and then analyze.

It's not optimal but you can import the data from that semantic model by using the Analysis Services connector.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.