PowerBI Query - transform, assign and move Data into other fitting cells

[Anonymous]

Hello Power BI Board,

its a little hard to describe my problem, but i have to following challenge infront of me:

I cleaned and transformed a Dataset into the following Dataset:

(the last column is not actually part of the initial data,i just needed to create it to piviot my data (maybe its somehow useful)).

Activity	Source	Target	Index
aaa	Word	Excel	1
bbb	Powerpoint	Power	1
ccc		Power	1
ccc		Power	2
ccc		Power2	3
ddd	SSystem1	TSystem1	1
ddd	SSystem2		2
ddd	SSystem3		3
eee	SSystem1		1
eee	SSystem2		2
eee	SSystem4		3
eee		TSystem2	1

(the empty cells show as (null) in the Query editor)

Now i need to do 2 things:

1. delete rows which dont have a Source orTarget System and therefore have no connection and cant be assigned to something

    (like the 3 rows in Activity "ccc" which only has Target Systems)

2. connect somehow the other systems through filling out the blanks correctly.

   (somehow fill in the blanks of the rows of activity "ddd" with "TSystem1" etc)

So essentially transform it into this:

Activity	Source	Target	Index
aaa	Word	Excel	1
bbb	Powerpoint	Power	1
ddd	SSystem1	TSystem1	1
ddd	SSystem2	TSystem1	2
ddd	SSystem3	TSystem1	3
eee	SSystem1	TSystem2	1
eee	SSystem2	TSystem2	2
eee	SSystem4	TSystem2	3

I am struggling a lot with this task so if anybody has any idea how to do this in the Query editor (what steps) or through new columns with DAX etc. i would be very thankful!

Strathos

[parry2k]

@Anonymous glad to hear it helped you to learn a few things. cheers!

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[parry2k · Accepted solution]

@Anonymous see attached, in PQ I have created a function called fnFillData that takes care of the logic you explained (I understood), you can tweak it as you see fit.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Anonymous]

Thx works like a charm, learned a lot from that:) Thx a lot for your help and great idea:)