Unable to open document - Multiple columns referencing a single source column

[alexeisenhart]

I have a large PBIX that I've been working on. The Desktop program was starting to feel a little glitchy -- changing a column's data type from Date/Time to Date was not removing the Time portion of the value, for example. To clean up those glitches, I saved the document and reopened it -- a good solution when the program starts to act funny.

When I tried to reopen the file, I got this message:

I understand well enough what the error is telling me. I don't know why this happened. If I could open the document, I could fix it, but...

1. I can't open the document in Power BI Desktop becaues of this error.
2. I can't open it in Tabular Editor because it's a PBIX.
3. PBI Explorer opens the file, but this is a readonly program.

Any ideas? Are there any programs like Tabular Editor that would allow me to delete a relationship and then save the file?

I could restore to an older version of the file, but I'd lose a few hours of work doing this.

[alexeisenhart · Accepted solution]

Thanks @vanessafvg for the idea! It didn't pan out -- the model file complained of the the same issue, and the report file had some other weird error reported. But you got me thinking.

I found a solution. I'm documenting it here as fully as I can so that it can help someone (maybe me!) in the future.

I downloaded the latest version of PBI Tools. https://pbi.tools/ and hoped that it would read the file -- it DID! I used this command to dump the contents of the PBIX to a folder:

pbi-tools.exe extract "My Power BI file name.pbix"

Then I found the dataset that had the issue with 2 relationships from one table, and deleted one of the columns.  I opened the correct .tmd file in the tables folder, found the lines that described that column and deleted it. Here's what I actually changed:

	measure 'Project Status - Latest Description' =
		
		
		// CODE HERE
		lineageTag: b59ee9b3-a0f7-4725-a86c-91a9114896ec

	column regardingobjectid
		dataType: String
		isDataTypeInferred: false
		lineageTag: 2d6329c3-3c56-40c3-9c38-faf69a0758c0
		sourceColumn: "Software Project ID"
		summarizeBy: None

		annotation SummarizationSetBy = Automatic

	column id
		dataType: String
		isDataTypeInferred: false
		lineageTag: 9423b262-4d9d-4a03-9570-354c0cb47c5a
		sourceColumn: id
		summarizeBy: None

		annotation SummarizationSetBy = Automatic

I deleted everything from "column regardingobjectid" through "annotation SummarizeSetBy = Automatic" and also the empty lines, to preseve the formatting pattern. I saved the file and then ran this command:

pbi-tools.exe compile "My Power BI File Name Here" -format PBIT -outPath test.pbit

This command used the dump folder, which I modified, and built a PBIT from that file. I opened the PBIT and Power BI loaded and started to refresh the data. It failed because of the issues I just created with that dataset, but I was able to jump into Transform Tools and adjust the column names.

Issue Summary:

IDK how it happened, but somehow two columns had similar names and they both were trying to be used for one relationship to another table. This caused all sorts of weird runtime issues with Power BI Desktop. I saved the file and tried to re-load it, but the error prevent the file from opening. Tabular Editor would not open the file, because it was a PBIX.

Resolution:

I used PBI-Tools to create a dump of the contents of the PBIX, modified that extract, and rebuilt a PBIT that had more easily-fixable errors that Power BI Desktop was able to load. Fixed the original issue via Transform tools and hit Refresh.

Profit.

[MiguelTravessa]

just want you to know this was super usefull to me today! thank you man!

[vanessafvg]

nice one.

[alexeisenhart]

Hello again! I just ran into this issue today (https://powerusers.microsoft.com/t5/Power-Query/PowerBI-changes-my-dataverse-column-names/m-p/194636...), and that is how the issue in this post was created.

This happened with my primary key in a table. I renamed the column name then something happened with this feature (the one mentioned in the link above) and the key column was automatically renamed to the Dataverse name. My old relationship still existed, but to a column that didn't exist. So I went into the Power Query Editor, made a copy of my primary key column but renamed it to the default dataverse name, reloaded and made a new relationship. It worked, but Power BI was acting buggy. So I saved the file and reopen -- two relationships exist between two tables; auto-close.

At runtime, it LOOKED like there was one column with one relationship, but it was two columns with the same name (or same display name at least) each with their own relationship to the same table.

[vanessafvg]

i wouldn't say so because they wouldn't be supported, in power bi or desktop is the only way too make changes that is supported, that is why pbi explorer doesn't allow you to change anything.

what are you trying to save in the changes you is it model related or report related?  if its report related as in visuals then you might be able to split the file and save the report, however it sounds to me if you can't open the file the model is buggered which is the dataset.   Otherwise you will need to restore.

https://powerbi.tips/2020/06/split-an-existing-power-bi-file-into-a-model-and-report/