Self Sourcing Query and Data Refresh issue - Excel

[lardo5150]

So, here is what I am trying to accomplish:

I have a tool, that exports a list of cases that an engineer closed.  

Things include case number, time, etc.

The tool goes back 180 days.

If I were to export this every day, the new file will be different from the one I did the previous day.

created files will have the same column headers each time.

OwnerEmail,CompanyName,InternalTitle,ClosedDateTime

So here is what I am looking to do, in order to import new data each time, while preserving existing data and keep from creating duplicates.

First import of this file called ClosedCases.xls

Each time this file is created it is called the same thing, ClosedCases.xls

Tomorrow, the file is created, and we import it (would like it to be a data refresh so it is scheduled).

We look at the ClosedDateTime.  If any of those values match what is in the TARGET (our main file we are using), then we do not import those rows from the SOURCE (the newly created file).

We then import everything else.

I was following @ikalawski link he gave me below:

https://www.thebiccountant.com/2016/02/09/how-to-create-a-load-history-or-load-log-in-power-query-or... 

I am having a potential of two issues.

First, when I tried to point the spreadsheet at itself, I kept getting an error.

So I followed his steps in that article.

Created a Duplicate of CasesClosed.  I renamed it to ClosedResults

I set it up to append CasesClosed.

The source though for ClosedResults, is pointing to the same source as CasesClosed.  So I found it was just an exact copy of the data, it was not appending it.

In his steps, he has you changing the source to:

= Excel.CurrentWorkbook(){[Name="Result"]}[Content]

"result" being what he used, but I am assuming this would be ClosedResults for me.

the problem is no matter how I change it, I keep getting errors.

The source for CasesClosed and Closedresults looks like this right now:

= Excel.Workbook(Web.Contents("https://microsoft-my.sharepoint.com/something/something/Documents/ClosedCases.xlsx"), null, true)

I tried a few ways, but kept getting an error bout 0 arguments were passed to a function which expects 1 and 2

So I created a copy of the ClosedCases excel file in the same one drive folder I am pulling from and called it ClosedResults.xls.

I then changed it in PowerBi.

That seems to be working......

In the desktop PowerBi, I can do a data refresh.  It seems to be working.

When I publish it up to the Service though and try to do any kind of refresh, it fails immediately.

Data source error:

{"error":{"code":"ModelRefresh_ShortMessage_ProcessingError","pbi.error":{"code":"ModelRefresh_ShortMessage_ProcessingError","parameters":{},"details":[{"code":"Message","detail":{"type":1,"value":"Information is needed in order to combine data"}}],"exceptionCulprit":1}}} Table: ClosedResults.

[v-eachen-msft]

Hi @lardo5150 ,

According to the error message "Information is needed in order to combine data", you need to edit credentials and set the privacy settings in Power BI Service. Then keep the privacy is same in desktop and service.

Here is a similar case for your reference.

https://community.powerbi.com/t5/Service/Information-is-needed-in-order-to-combine-data/m-p/574058

[lardo5150]

I will add that under permissions for the datasets, privacy was set to None.

I changed this to Organizational for each of them.

Again, updates on Desktop, but errors in Service.

[lardo5150]

Just changed Privacy settings to NONE for everything.

Still same result.

[lardo5150]

So I tried to go back and modify the datasource EXACTLY how it says in the article I posted.

When I change the source of the duplicate from this:

= Excel.Workbook(Web.Contents("https://microsoft-my.sharepoint.com/something/something/something/something/ClosedCases.xlsx"), null, true)

and change it, I get the following errors, depending on how I change it:

[lardo5150]

Bumping this as I am stuck.

I have been messing with this all day.

I could really use some help on this.

Again, my objective is to keep a running log.

[Sumandega]

I had the same problem, I tried all the solutions described above and they didnt work, what worked for me was publishing the report and dataset to a new workspace that I created as an admin after finding out that I was a member on the previous workspace.