Create new row in one table from a column in another table at set intervals

[rmwwgs]

Hello, 

I am wanting to create a table that adds a new row every Sunday or failing that, can add a new row when refreshed, pulling values from another table. I have a different table that shows the number of house points that have been awarded since last Sunday (which are pulled through from an SQL database) and have created a column that ranks the rows from worst to best. There are 4 houses so the top house ends up with 4, the worst with 1. I need to be able to find a way to extract this column and add it as a row, along with the date of the extraction, into a separate table.

So the original table looks like this:

AcademicHouse	Points	Rank
House A	25	4
House B	13	1
House C	14	2
House D	18	3

And the new table will look like this, with a new row added every week:

House A	House B	House C	House D	Date
4	1	2	3	16/05/2021
3	4	1	2	23/05/2021
3	2	4	1	30/05/2021

This way I can calculate a running total of the weekly points.

Thanks,

Rob

[v-angzheng-msft]

 Hi, @rmwwgs 

You can try to use the pivot column feature in Power Query if you already have a ranked column

If the ranking column is a calculated column that you created and may not show up in Power Query, then try ranking it in Power Query and use the pivot column feature.

Assuming you don't have a ranking column, let's work in Power Query

1. Copy the source table
2. Click Add Column > Click Date > Click Week > Click Start of Week
3. Delete the date column
4. Group ranking
5. Delete the Points column
6. Pivot column

Group ranking:

= Table.AddColumn(a, "Rank", each Table.RowCount(Table.SelectRows(a,(x)=>x[Start of Week]=[Start of Week] and x[Points]>[Points]))+1)

Result:

Have rank column

Don’t have rank column

Please refer to the attachment below for details

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rmwwgs]

This is great, but its the very first step that I need. We have been currently adding data in manually into a spreadsheet at the end of each week and I am looking for a way to automate it. In the attachment the Source_DatteCouldbeDiff table has been created manually, adding the scores in next to the date for each week. I'm looking to see if there is a way to create that table automatically, adding the new row each week.

[v-angzheng-msft]

Hi, @rmwwgs 

I'm very happy that this solution completes one of your steps, if this solves your problem above and you adopt my solution I would be very happy, I'm very sorry if I misunderstood your reply.

But if this requirement is different from the last one, a better way is to start a new thread and describe your requirement in detail, preferably with screenshots or sample data without sensitive information, I'm also very happy to follow up your problem in a new thread, again, I'm very sorry if I misunderstood your meaning.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rmwwgs]

Don't worry about it! It does seem like you got the idea of what I was going for, but skipped the part I was having trouble with.

I want to automate the process of pulling the scores from my original table out every week and inserting them into the new table in the format that you have. In your example, you jumped straight to that table without showing how it could be created automatically.

[v-angzheng-msft]

Hi, @rmwwgs 

I apologize for my misunderstanding, I thought what you wanted to achieve was to get from A to B.

So how does your original table look like, how does your A and B look like, can you show your desired output, I believe if I can understand your desired output correctly I can find relevant ideas.

Best Regards,
Zeon Zheng

[rmwwgs]

Hi again Zeon,

That is right, but I want to process to be automated so that each new row in table B would be created at the start of a new week. We are currently doing it ourselves manually but it would be really helpful to not have to do it ourselves every week!

Thanks

Rob

[Anonymous]

Hi @rmwwgs 

  RELATED() function can be used to refer the value from another table and since this function operates in row context you need to create calculated column.I recently provided solution for the similar problem, pls refer this post.

https://community.powerbi.com/t5/Desktop/Create-a-measure-that-looks-at-the-first-column-and-if-blan...

Thanks
Raj

[rmwwgs]

This is looking good, just need a way to return the column as a row.