Issue on loading data from Power Query on generating pbix file by automation

[sheetalshettiga]

Hi all,

I am trying to create a .pbix file by automation that is when u unzip a .pbit we get a DataModelSchema and DataMashup file I am feeding in the content to these file from a json metadata and trying to zip back the files and create a .pbix file.

I am facing a issue on automating the joins details.I feed in the join details in DataMashup file with the required MQuery syntax but the merged columns are not being updated or loaded on DataModelSchema file on generating a .pbix file out of them .

For ex PRODUCT_CATEGORIES will have CATEGORY_ID an CATEGORY_NAME column when I merge it with PRODUCT table by automation(i.e creating Mquery in  DataMashup by getting metadata from json) the merged columns from PRODUCTS are not loaded to PRODUCT_CATEGORIES table  hence if I want to automate the relationship and try to use PRODUCT_ID column from PRODUCT_CATEGORIES(which is merged with PRODUCTS and got the PRODUCT_ID column ) on opening the zipped back .pbix I get a error saying referenced column in relation does not exists(PRODUCT_ID not found in PRODUCT_CATEGORIES). The reason is whatever the Mquery I feed in to DataMashup using automation it will not update them in DataModelSchema file hence the merged columns are not being updated in DataModelSchema file and I get the error referenced column in relation does not exists.

Is this the load issue in power BI while generating .pbix file using automation so the Mquery steps are not being applied in DataModelSchema file?

Hence one more scenario can be ..if I try to rename a column by automation in MQuery it will not be updated in DataModelSchema file,the column name will be old name as well..

How can I solve this issue so that I can  automate the pbix file creation. so that the MQuery steps are being updated in DataModelSchema file as well.

[ImkeF]

Hi @sheetalshettiga ,

don't understand where the renaming issue is coming from now.
Would recommend that you're starting to try out some of the methods I've suggested. 

And keep is as simple as possible.

[ImkeF]

Hi @sheetalshettiga ,

have you tried any approaches of it meanwhile?
I might have not been clear enough, but one way to do this without any additional hacking in the code would be to create the future columns already in the query editor and just leave them blank or fill with dummy values. But make sure to give them the desired format/types already.

This will trigger them appearing in the datamodel schema file and you can then just replace the M-code like before.

[sheetalshettiga]

Hi @ImkeF

will creating a future column be accurate?

Suppose we have done renaming a column then the new column name should also be updated in DataModelSchema file?

[ImkeF · Accepted solution]

Hi @sheetalshettiga ,

I've studied your problem description. 

Reason is probably that there are no column metadata for the new columns.

You could either try to fill them in by hand in the DataModelSchema file or instead create a dummy column for the new column (ideally with the correct format already) and load that with blank values.
This would ensure that the new columns are included in the datamodel and can then just be filled with the correct values afterwards.

[sheetalshettiga]

@ImkeF  will zipping back to .pbix file or apply changes  on powerbi not do the work for me?Do I need to manually fill the new columns in DataModelSchema file?

[ImkeF]

@sheetalshettiga ,

that could be the reason,

there have been many changes in the past.
You can check if there is duplicate M-code in the DataModel as well, it would probably be under tables - partitions:

#"Parsed JSON" = Json.Document(Source),
model = #"Parsed JSON"[model],
tables = model[tables],
tables1 = tables{0},
partitions = tables1[partitions],
partitions1 = partitions{0},
source = partitions1[source]

Unfortuantely I'm not aware of any other alternative currently. It looks as if programmatically creating pbix is a thing of the past with the new medatadata format.

[sheetalshettiga]

@ImkeF  There is no duplicate M code in the partition in DataModelSchema file

[ImkeF]

Hi @sheetalshettiga ,

which Desktop version are you using?

With newly created files in the current PBI Desktop version, your approach is not possible any more. You will always get error-messages about file corruption. This is because the metadata format for pbix files has been changed recently.

If you use an old file with the current version, you might even have duplicate M-code: 1 in the Mashup and for the loaded tables there might event be M-code in the DataModelSchema File.
So you would have to make the changes in the 2 areas then.
But this is all totally unsupported and I would not wonder, if that would fail as well.

[sheetalshettiga]

@ImkeF I am using july version itself since DataModelShema and DataMashup files are available there when we unzip a .pbit. Is this the loading issue so Mquery steps are not being applied on DataModelSchema file?Any other way of handling it?