Issue Importing Two Tables from Power BI Datasets

[technolo_bi]

Hello Everyone,

I am encountering an issue which I haven't faced before. I have two different reports in Power BI that contain a number of tables.

The tables in these reports have been created using SQL queries in order to pull data from our database.

I am trying to import two tables from Table A into Table B, however when I try to do this, I receive the error of "This table cannot be shown because it is not in import mode.' 

I initially thought to also import the two tables as Excel and then simply import them into the other report however that is not really a possibility as the tables have about 9 million rows and they get updated weekly so I would lose this connection between the two.

I would appreciate any sort of help as I have been battling with this for some time.

Thanks again 😄

[joaoribeiro]

Hi @technolo_bi !

Can you please share a screenshot of your problem? I also have a couple of questions:

1. When you say "I am trying to import two tables from Table A into Table B" you mean that you are trying to import tables from Report A to Report B?
2. If so, I think you need a DataFlow to create all the connections and transformations you need in the service and then connect the tables to any report you need. Another option is to recreate the same query that in have in Report A in the Report B.
3. If not, can you please elaborate what exactly you are trying to do?
   
   Hope this answer helps you with your problem!
   If you need any additional help please @ me in your reply.
   If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
   
   Thanks!
   
   Best regards,
   Joao Ribeiro

[technolo_bi]

Hello Joao,

Thank you so much for getting back to me.

So the Report that I am trying to get the tables from is quite complicated.

We have Report A which contains these two tables, however Report A is using another Report in order to get some forecasting calculations.

This is the table containing the calculations: = AnalysisServices.Database("powerbi://api.powerbi.com/v1.0/xxx/yyyy", "Forecast Tool calculations", [TypedMeasureColumns=true, Implementation="2.0"])

= Cube.Transform(Model2,
{
{Cube.AddAndExpandDimensionColumn, "[DimArticle]", {"[DimArticle].[Cons Fix].[Cons Fix]", "[DimArticle].[Cons Min Date Sold].[Cons Min Date Sold]"}, {"Cons Fix", "1st sale date of cons"}},
{Cube.AddMeasureColumn, "Available (pcs)", "[Measures].[Available (pcs)]"},
{Cube.AddMeasureColumn, "Item sold in CY (pcs)", "[Measures].[Item sold in CY (pcs)]"},
{Cube.AddMeasureColumn, "Item sold in CY-1 (pcs)", "[Measures].[Item sold in CY-1 (pcs)]"},
{Cube.AddMeasureColumn, "Item sold in CY-2 (pcs)", "[Measures].[Item sold in CY-2 (pcs)]"},
{Cube.AddMeasureColumn, "OOS CY (weeks)", "[Measures].[OOS CY (weeks)]"},
{Cube.AddMeasureColumn, "OOS CY-1 (weeks)", "[Measures].[OOS CY-1 (weeks)]"},
{Cube.AddMeasureColumn, "OOS CY-2 (weeks)", "[Measures].[OOS CY-2 (weeks)]"},
{Cube.AddMeasureColumn, "OOS YTD LY (weeks)", "[Measures].[OOS YTD LY (weeks)]"},
{Cube.AddMeasureColumn, "Forecast sales next 12m (pcs)", "[Measures].[Forecast sales next 12m (pcs)]"},
{Cube.AddMeasureColumn, "Forecast sales next 6m (pcs)", "[Measures].[Forecast sales next 6m (pcs)]"},
{Cube.AddMeasureColumn, "Forecast sales YTG (pcs)", "[Measures].[Forecast sales YTG (pcs)]"},
{Cube.AddMeasureColumn, "Sales monthly volume AVG", "[Measures].[Sales monthly volume AVG]"},
{Cube.AddMeasureColumn, "Sales volume CY", "[Measures].[Sales volume CY]"},
{Cube.AddMeasureColumn, "Sales volume CY-1", "[Measures].[Sales volume CY-1]"},
{Cube.AddMeasureColumn, "Sales volume CY-2", "[Measures].[Sales volume CY-2]"},
{Cube.AddMeasureColumn, "Sales volume last 30 days (daily AVG) CONS", "[Measures].[Sales volume last 30 days (daily AVG) CONS]"},
{Cube.AddMeasureColumn, "Sales volume last 30 days LY YTD (daily AVG) CONS", "[Measures].[Sales volume last 30 days LY YTD (daily AVG) CONS]"},
{Cube.AddMeasureColumn, "Sales volume YTD LY", "[Measures].[Sales volume YTD LY]"},
{Cube.AddMeasureColumn, "Total Items Sold (pcs)", "[Measures].[Total Items Sold CONS]"},
{Cube.AddMeasureColumn, "Growth Rate", "[Measures].[_Growth Rate]"},
{Cube.AddMeasureColumn, "ETA (qty)", "[Measures].[ETA qty CONS]"},
{Cube.AddMeasureColumn, "OOS Prediction Date", "[Measures].[OOS Prediction Date]"},
{Cube.AddMeasureColumn, "OOS Prediction Status", "[Measures].[OOS Prediction Status]"},
{Cube.AddMeasureColumn, "PTD (none ETA) (qty)", "[Measures].[PTD qty (none ETA) CONS]"},
{Cube.AddMeasureColumn, "Total articles (Stock + ETA + PTD) (pcs)", "[Measures].[Total articles (Available + ETA + PTD)]"},
{Cube.AddMeasureColumn, "Forecast 1st order (order now and cover next 5m)", "[Measures].[Forecast 1st order (order now and cover next 5m)]"},
{Cube.AddMeasureColumn, "Forecast 2nd order (order in 2m and cover next 2m)", "[Measures].[Forecast 2nd order (order in 2m and cover next 2m)]"},
{Cube.AddMeasureColumn, "Forecast 3rd order (order in 4m and cover next 2m)", "[Measures].[Forecast 3rd order (order in 4m and cover next 2m)]"},
{Cube.AddMeasureColumn, "Forecast 4th order (order in 6m and cover next 3m)", "[Measures].[Forecast 4th order (order in 6m and cover next 3m)]"},
{Cube.AddMeasureColumn, "Number of Days to OOS", "[Measures].[Number of Days to OOS]"},
{Cube.AddMeasureColumn, "Forecast 10M", "[Measures].[Forecast 10M]"},
{Cube.AddMeasureColumn, "Forecast 11M", "[Measures].[Forecast 11M]"},
{Cube.AddMeasureColumn, "Forecast 12M", "[Measures].[Forecast 12M]"},
{Cube.AddMeasureColumn, "Forecast 1M", "[Measures].[Forecast 1M]"},
{Cube.AddMeasureColumn, "Forecast 2M", "[Measures].[Forecast 2M]"},
{Cube.AddMeasureColumn, "Forecast 3M", "[Measures].[Forecast 3M]"},
{Cube.AddMeasureColumn, "Forecast 4M", "[Measures].[Forecast 4M]"},
{Cube.AddMeasureColumn, "Forecast 5M", "[Measures].[Forecast 5M]"},
{Cube.AddMeasureColumn, "Forecast 6M", "[Measures].[Forecast 6M]"},
{Cube.AddMeasureColumn, "Forecast 7M", "[Measures].[Forecast 7M]"},
{Cube.AddMeasureColumn, "Forecast 8M", "[Measures].[Forecast 8M]"},
{Cube.AddMeasureColumn, "Forecast 9M", "[Measures].[Forecast 9M]"},
{Cube.AddMeasureColumn, "Sales volume 12M (weekly AVG)", "[Measures].[_Sales volume 12M (weekly AVG)]"},
{Cube.AddMeasureColumn, "Sales volume 12M-12 (weekly AVG)", "[Measures].[_Sales volume 12M-12 (weekly AVG)]"},
{Cube.AddMeasureColumn, "Growth Rate TEST", "[Measures].[_Growth Rate TEST]"}
})

And this is the added items column.

For the second table we have the following:
= AnalysisServices.Database("powerbi://api.powerbi.com/v1.0/xxx/yyy", "Forecast Tool calculations", [TypedMeasureColumns=true, Implementation="2.0"])

And the added columns have the following formula:
= Cube.Transform(Model2,
{
{Cube.AddAndExpandDimensionColumn, "[DimArticle]", {"[DimArticle].[Active Article].[Active Article]", "[DimArticle].[Active Article Tag].[Active Article Tag]", "[DimArticle].[Article link].[Article link]", "[DimArticle].[Article Name].[Article Name]", "[DimArticle].[Article Name or Cons Name].[Article Name or Cons Name]", "[DimArticle].[Article photo].[Article photo]", "[DimArticle].[Article Tag].[Article Tag]", "[DimArticle].[Articles in cons].[Articles in cons]", "[DimArticle].[Cannot release on ramp].[Cannot release on ramp]", "[DimArticle].[Category per Active Article].[Category per Active Article]", "[DimArticle].[CBM].[CBM]", "[DimArticle].[Column].[Column]", "[DimArticle].[Cons Deleted].[Cons Deleted]", "[DimArticle].[Cons Fix].[Cons Fix]", "[DimArticle].[Cons link].[Cons link]", "[DimArticle].[Cons Min Date Sold].[Cons Min Date Sold]", "[DimArticle].[Created date].[Created date]", "[DimArticle].[Deleted].[Deleted]", "[DimArticle].[Deleted if AVA 0].[Deleted if AVA 0]", "[DimArticle].[Deleted if Stock 0].[Deleted if Stock 0]", "[DimArticle].[ID Article].[ID Article]", "[DimArticle].[ID Article Txt].[ID Article Txt]", "[DimArticle].[Is Active Article].[Is Active Article]", "[DimArticle].[Min Date Sold].[Min Date Sold]", "[DimArticle].[Most recent Order Date].[Most recent Order Date]", "[DimArticle].[NOT Replacement parts in cons].[NOT Replacement parts in cons]", "[DimArticle].[Product Manager].[Product Manager]", "[DimArticle].[Product Manager Active Article].[Product Manager Active Article]", "[DimArticle].[Product Status].[Product Status]", "[DimArticle].[Replacement Part].[Replacement Part]", "[DimArticle].[Replacement parts in cons].[Replacement parts in cons]", "[DimArticle].[Spare Part].[Spare Part]", "[DimArticle].[Spare Part Active Article].[Spare Part Active Article]", "[DimArticle].[Supplier Name].[Supplier Name]", "[DimArticle].[Supplier Name Active Article].[Supplier Name Active Article]", "[DimArticle].[Total sold article].[Total sold article]", "[DimArticle].[Volume per shipping unit].[Volume per shipping unit]", "[DimArticle].[Volume per single unit].[Volume per single unit]"}, {"DimArticle.Active Article", "DimArticle.Active Article Tag", "DimArticle.Article link", "DimArticle.Article Name", "DimArticle.Article Name or Cons Name", "DimArticle.Article photo", "DimArticle.Article Tag", "DimArticle.Articles in cons", "DimArticle.Cannot release on ramp", "DimArticle.Category per Active Article", "DimArticle.CBM", "DimArticle.Column", "DimArticle.Cons Deleted", "Cons Fix", "DimArticle.Cons link", "1st sale date of cons", "DimArticle.Created date", "DimArticle.Deleted", "DimArticle.Deleted if AVA 0", "DimArticle.Deleted if Stock 0", "DimArticle.ID Article", "DimArticle.ID Article Txt", "DimArticle.Is Active Article", "DimArticle.Min Date Sold", "DimArticle.Most recent Order Date", "DimArticle.NOT Replacement parts in cons", "DimArticle.Product Manager", "DimArticle.Product Manager Active Article", "DimArticle.Product Status", "DimArticle.Replacement Part", "DimArticle.Replacement parts in cons", "DimArticle.Spare Part", "DimArticle.Spare Part Active Article", "DimArticle.Supplier Name", "DimArticle.Supplier Name Active Article", "DimArticle.Total sold article", "DimArticle.Volume per shipping unit", "DimArticle.Volume per single unit"}}
})

Now, I have another report where I want to add these two specific tables. Whenever I try to add it using the get data from power bi datasets, I am able to import them, but whenever I try to open them I get this error. So they are unusable.

Apologies for providing so many lines of code and I appreciate a lot the fact that you got back to me.

Looking forward to hearing from you.

All the best

[technolo_bi]

@joaoribeiro 

[joaoribeiro · Accepted solution]

Hi @technolo_bi ,

Thank you for the further details, it's clear for me now. With that, my thoughts are:

1. Do you need to transform the data somehow, or just use it in the report?
   • If it's the latter, then you can use the data normaly using measures, relationships, etc. You just CAN'T do any kind of transformation, add/remove columns, etc., because it's a live connection.
   • If you need changes in the data model, then my suggestion is to make any needed transformation in the original dataset and then just use the data in the second dataset, creating measures and other allowed components, but not changing the data model.

Unfortunately there is no workaround for this situation, because when you connect to another Power BI dataset the data model is locked for editing.

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍

Thanks!

Best regards,
Joao Ribeiro

[Inna_S]

Thank you for your explanation. It helped me also.

[technolo_bi]

Hello Joao,

Yes, this does solve my problem.

Thank you so much for the help, I will mark it as fixed now.

All the best

[Syndicate_Admin]

I think your solution is more about implementing a DataFlow that centralizes the data in your main table.

Once synced, you could already use that data in two or more different datasets.