Ordering a Table from different Datasets

[PeteSil1020]

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.

[v-rongtiep-msft]

Hi @PeteSil1020 ,

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 _ Rongtie

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

[PeteSil1020]

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?

[v-rongtiep-msft · Accepted solution]

Hi @PeteSil1020 ,

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 _ Rongtie

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