Appending data from external semantic model

[plew]

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.
   

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:

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

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

[v-xinruzhu-msft · Accepted solution]

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.

[plew · Accepted solution]

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 · Accepted solution]

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]

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]

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.

[lbendlin]

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