Data update Error in Both Power BI Desktop and Service

[agragesh123]

Hi

I have some queries which are as below:

1)      I have daily orders files lying in one single folder in S3. Each day txn is a separate file.  How do I see a consolidated data of all the individual transaction file.

2)      I have kept modifying the transaction file in the past. So the column headers will differ. But certain key columns will always be there in the file.  How do I see the key data once the problem number 1 is resolved and I have to analyse certain fields of the consolidated file (and files will have different number and arrangement of headers)  

3)      I want to understand if it can continuously update the file and analysis when we add files to the folder for future days.

The error I get when I modify the data is 

Regards,

Agragesh 

[v-qiuyu-msft]

Hi @agragesh123,

1)      I have daily orders files lying in one single folder in S3. Each day txn is a separate file.  How do I see a consolidated data of all the individual transaction file.

2)      I have kept modifying the transaction file in the past. So the column headers will differ. But certain key columns will always be there in the file.  How do I see the key data once the problem number 1 is resolved and I have to analyse certain fields of the consolidated file (and files will have different number and arrangement of headers)  

What do you mean about "S3" and "txn"? There is a sample about combining multiple Excel and schedule refresh for your reference:
Loading Data From Multiple Excel Workbooks Into Power BI–And Making Sure Data Refresh Works After Pu....

3)  I want to understand if it can continuously update the file and analysis when we add files to the folder for future days.

You can add new files into this folder, the new coming files data in this folder should be also display in the report based on the Power Query. In your scenario, as the refresh failed with dataset, I suggest you open Query Editor in Power BI desktop, check if any error throws out.

Best Regards,
Qiuyun Yu

[agragesh123]

1)My data is in Amazon server (S3), and my daily order transaction(txn)  report is generated each day I want to combine all data like suppose yesterday data+ day before yesterday+ and so on into one file. 

I tried Merge quries  but it displays only 1 and if i expand column it only extends as a column and not adding to data.

2) If I have any changes in the data set like adding a column it throws an error while refershing "ABC" Column not found in data set.

I would also like to add I have a work around to both the above problems but it takes 1-2hrs to do so and I have to do it each time to make sure every thing is smooth .

Can this be automated some how?

If BI can just it self understand how many columns are there and change /add/remove column on it own it would be great.

[v-qiuyu-msft · Accepted solution]

Hi @agragesh123,

In Query Editor, when we have one or more columns that we'd like to add to another query, we can merge the queries use Merge Queries. When we have additional rows of data that we'd like to add to an existing query, we can append the query use Append Queries. In your scenario, it supposed that you can use Append Queries.

When we refresh the data, Power Query will be executed to transform data. Whether can add or delete columns depends on add or delete columns steps write in the Power Query. You can check underlying Power Query via Advanced Editor.

Best Regards,
Qiuyun Yu