Append Queries 3 different data sources

[Dklein]

Hello,

     Ive been provided the task in making a manual report automated. The hard thing about it is the information comes off 3 different data sources. 2 automated excel sheets which i have taken the SQL's off of and one smartsheet document I have data shuttled into an excel sheet to ensure all my reports used are running off automated excel. After reviewing how to mesh all the information together I see my best route is Appending the queries. Out of the 3 sheets, i need 15 columns. Now due to the information being different in each sheet as well as them all having there automation, how do I create a report where all the 15 columns will align like if it was one document? It is done manually at the moment hence the reason why it was not an issue before. 

If a more indepth explanation is required please let me know!

Best, 

Daniel  

[v-stephen-msft]

Thanks for everyone's insights.

When appending three queries in Power Query Editor, there are several considerations and limitations I would like to share with you:

1. Column Consistency: Ensure that the column names and data types are consistent across all queries. If columns don’t match, Power Query will fill in missing columns with null values.

2. Order of Queries: The order in which you append the queries matters. The first query you select will be the primary table, and subsequent queries will be appended to it.

3. Data Volume: Large datasets can impact performance. Consider filtering or aggregating data before appending to improve efficiency.

4. Data Refresh: When you refresh your data, all appended queries will be refreshed. Ensure that all data sources are accessible and up-to-date.

5. Error Handling: Be prepared to handle errors that may arise from data inconsistencies or missing columns. Implement error-checking steps to ensure data integrity.

6. Transformation Steps: If you need to perform transformations on the data, it’s often best to do so before appending the queries. This can help maintain a clean and efficient workflow.

By keeping these considerations in mind, you can effectively manage and append multiple queries in Power Query Editor.

Reference link:

Append queries - Power Query | Microsoft Learn

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dklein]

Hello, 

    Really appreciated the detailed response. So This is where i have landed. 

- Made sure all the columns i took were refreshed / matching the original query (names)

- I made sure to add the queries in the order they are in, in the manual report. 

Now to be clear so everyone understands. Im dealing with over 161,000 Lines. The information across all three sheets do not have the same information listed. Certain columns match to a certain degree. Ex: I took the number of total Owners from one sheet and the other appended columns entere only had Owners who have had work completed. Similar info but different volume of lines. At the moment, only the original query is working. On the appended query, no other columns show green except the first couple. In query dependcies, it show me now that all queries are loaded since i updated the names on all queries. I personally think due to the amount of lines and having data that does not correlate to eachother, it will not work. 

I am very lost in this and not sure what to do as even though it is all in one query, i cannot create a visual correctly with the columns that are from seperate sheets but are now in the one query. 

Please let me know if i should stop here and re think or if there is a light at the end of the tunnel. 

Best,

Daniel 

[Dklein]

Thank you for the information. 

I have done excatly what was told but now only one of the query columns are showing green (working) the rest are not showing information. When i checked query dependencies, it shows the appended is not loaded. 

I am newer to Power BI so I apologize if this is basic functions. 

Please let me know!

Best,

Daniel 

[MaheshkumarSuma]

In the left pane of the Power Query Editor, right click on the dataset and ensure that Ensure Load is checked

[Dklein]

Yes it is! the only information showing is the original query I asked to append the rest of the information. the rest is in Null. I have no other errors or anything popping up or showing it is an issue. 

[MaheshkumarSuma]

Power BI forces you to match the column names and not just the order while appending because for example when t1_col1, t2_col1 and t3_col1 are appended, it wouldn't know which column name to keep after appending. Anyway in your report, you would only be able to pick one column name to display in your report header after appending the 3 different columns from 3 sources. 

[MaheshkumarSuma]

Can you show me a screenshot of how it looks?

[jgeddes]

Ok. Let's start from the beginning to see if we can find the issue.
Here is an example...
I have three queries tableOne, tableTwo, and tableThree...

If I now create a fourth query using 

Table.Combine({tableOne, tableTwo, tableThree})

I would end up with...

Now, based on your intial post this is not the result you are looking for.

So if, in each of my source tables, I rename my columns to "first" and "second" the resulting combined query should now look like this...

This works because each of the source tables now have the same column names. 
If your result is not similar to the above it could indicate that your column names do not exactly match or the table references in your Table.Combine statement are incorrect. 

I have attached a pbix file with this example for you to review.

[MaheshkumarSuma]

Okay I see what you mean now. Power BI forces you to match the column names and not just the order while appending because for example when t1_col1, t2_col1 and t3_col1 are appended, it wouldn't know which column name to keep after appending. Anyway in your report, you would only be able to pick one column name to display in your report header after appending the 3 different columns from 3 sources

[jgeddes · Accepted solution]

The short version of the answer is to create a query for each of your data sources, perform all the neccessary transformations to the source(s) so that they can be appended to the others. Set all of these queries so they do not load into the model. Create a fourth query that has the source equal to Table.Combine({yourFirstQuery, yourSecondQuery, yourThirdQuery).
This approach allows you to manipulate each source seperately so that it can be append correctly.

[MaheshkumarSuma]

Here are the steps to report the 15 columns from the three Excel data sources:

1. Get Data > Excel > select your workbook > Transform Data.
2. Click on Choose Columns or Remove Columns and select only the 15 columns you need.
3. Repeat the same process for the other two datasets.

Once you have imported all three datasets into Power Query:

1. Ensure that the 15 columns in all three datasets are in the same order.
2. Click on Append Queries.
3. Merge the first two datasets (selecting Append Queries without choosing Append Queries as New) and rename the combined dataset.
4. Repeat this process with the renamed dataset and the third dataset.

After all the data is appended, click on Close & Apply and proceed to create a table visual for your reporting needs.