Adding Rows to a Table from Another Table

[Omar1]

I'd like to create an Account Activity table that shows every activity from each account to it. Currently, I have an Account table where it shows only the latest activity. The Account table gets updated on a daily basis. Ideally, I'd like to add the latest account activity from the Account table to the Account Activity table as an individual row.

Example:

- Monday: Account #123 added 3 contacts

- Tuesday: Account #123 added 4 contacts

The Account table in the latest refresh would show the last activity as "added 4 contacts"....I'd like to have the Account Log table show both rows.

Is that possible in Power BI? If so, how?

Thanks!

[v-juanli-msft]

Hi

which is the relationship between Account Activity table and the Account table, does they have a key column such as account?

As you said, “The Account table in the latest refresh would show the last activity as "added 4 contacts"”, so the example of Monday and Tuesday should show in the rows of Account Activity table, right?

Whether we could add rows to a table from another table depends on the table structure. So, if it is convenient for you, please share me screenshots or files to know how the table structure.

I also confused by this sentence “I'd like to have the Account Log table show both rows”, does the Account Log table refer to the Account table above?

Best Regards

Maggie

[Omar1]

Thank you for helping out, it's very kind of you!

The relationship between Account and Account Activity tables will be the AccountId.

In the example above, that's what my Account table will look like where the last column is FrienshipLevel.

The problem is that with this table above, the FriendshipLevel changes, but I would like to track each change for each account using Power BI.

For example, if I change Joe Smith's FriendshipLevel from 5 to 13 today....the LastModifiedDate will be today's date and I will lose what the FriendshipLevel was before.

That is where I was hoping the Account Acitivity Table comes in because it would show something like this...

[parry2k]

I guess you have two table account and account activity, bring both to the data model and set relationship on ID between tables.

You can see all the history of selected ID (employee) from your account table.

[Omar1]

Currently, I only have one table (below).

I was wondering, can I use Power BI to create this table (below) from the data in the above table?

I'm sorry for not making it clear.

[parry2k]

Sorry man, not sure what you have and what not?

So you have account activity table but no account table or other way?

[Omar1]

I have the account table and don't have the account activity table.

I was hoping to create an account activity table from the data in the account table.

[parry2k]

Not sure how that is even possible, if your data model doesn't have the data, I don't htink PowerBI can generate data. Think logically how PowerBI will now what was the previous friendshiplevel and when it was changed, may be I'm missing something here.

[Omar1]

You're absolutely right and I understand that part.

My assumption was that since the data refreshes on a daily basis in Power BI, there can be a validation that checks if the LastModifiedDate for an account in the Account table is greater than the ModifiedDate for the same account in the Account Activity table. If it's not, then it adds the account data from the Account table to the Account Activity table as a new row.

Is that possible in Power BI?

[parry2k · Accepted solution]

Nope, I guess with some R code this can be done but I don't think PowerBI is used to write back. You have to work on database end to take care of this.

[Omar1]

Thank you for letting me know. I appreciate your help!