Forum Discussion
Ordering a Table from different Datasets
Hello,
This has me quite stumped on how to make this possible, but I am going to do my very best at explaining what I am trying to achieve.
Dataset P6:
| Name | Status |
| Henry | P6: Last Update |
Dataset P5:
| Name | Status |
| Jill | P5: Almost Last Update |
| Jack | P5: Almost Last Update |
Dataset P4:
| Name | Status |
Dataset P3:
| Name | Status |
Peter | P3: Almost First Update |
| Andrew | P3: Almost First Update |
| Casey | P3: Almost First Update |
Dataset P2:
| Name | Status |
| Ryan | P2: Near First Update |
GOAL:
Create a table that counts all the rows and puts the Name's in order based off Status without showing the status, and adding the row number before each name, while also adding the word UPDATE after each name. It should also check all datasets, and if blank move on to the next dataset.
1. Henry UPDATE |
| 2. Jill UPDATE |
| 3. Jack UPDATE |
| 4. Peter UPDATE |
| 5. Andrew UPDATE |
| 6. Casey UPDATE |
| 7. Ryan UPDATE |
Any insight on how to accomplish in the easiest and best manner will be greatly appreciated.
Hi Anonymous ,
Please learn more about append.
The append operation creates a single table by adding the contents of one or more tables to another, and aggregates the column headers from the tables to create the schema for the new table.
When tables that don't have the same column headers are appended, all column headers from all tables are appended to the resulting table. If one of the appended tables doesn't have a column header from other tables, the resulting table shows null values in the respective column.
More details: Append queries - Power Query | Microsoft Learn
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Anonymous ,
I have created a simple sample, please refer to my pbix file to see if it helps you.
Append the tables.
let Source = Table.Combine({#"Dataset P6", #"Dataset P5", #"Dataset P4", #"Dataset P3", #"Dataset P2"}), #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type), #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Status"}), #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each "update"), #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Index", "Name", "Custom"}) in #"Reordered Columns"How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you very much for your response. This is very helpful! Now I have one slight issue. All the data I have right now is currently in this form:
Name Status Jill P5: Almost Last Update Jack P5: Almost Last Update Peter P3: Almost First Update Casey P3: Almost First Update Ryan P2: Near First Update Henry P6: Last Update Andrew P3: Almost First Update And my issue is that I know there will be data from other P values that aren't showing, so how can I prepare for those?
Hi Anonymous ,
Please learn more about append.
The append operation creates a single table by adding the contents of one or more tables to another, and aggregates the column headers from the tables to create the schema for the new table.
When tables that don't have the same column headers are appended, all column headers from all tables are appended to the resulting table. If one of the appended tables doesn't have a column header from other tables, the resulting table shows null values in the respective column.
More details: Append queries - Power Query | Microsoft Learn
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.