Problem with duplicates

[Anonymous]

Hello, I need some help with data transformation. I have table like below:

And I need to have all data in one row for cases with new o revisit not blank. This is hard becouse there could be many revisits with te same id... My idea was to find first case assigned (or data suffucient or case done) date which is bigger than new case date  for each id, but i do not know how to implement that in power BI (i've tried vlookup bu it doesnt work becouse of duplicates)

Please help!

[Greg_Deckler]

@ImkeF might have some Power Query magic that could help with this. Can you provide your expected output from the data presented, I'm not sure I'm clear on exactly what you are going for. 

[Anonymous]

Hi,

I've extracted one of problematic cases in excel, this smaller table is my expected output.

[v-shex-msft]

Hi @Anonymous,

It will be help if you share some sample data for test.

You can direct paste sample data here with table formula or upload sample file to onedrive and share link here.

Regards,

Xiaoxin Sheng

[Anonymous]

sCRM_ID	New_or_revisit	Index	New case	Revisit	Case assigned	Data sufficient	Case done
17.KR.784811	New	24	03.08.2018 11:12
17.KR.784811		25				03.08.2018 11:24
17.KR.784811		26			03.08.2018 11:24
17.KR.784811		31					03.08.2018 13:14
17.KR.784811		95				08.08.2018 08:54
17.KR.784811		96			08.08.2018 08:54
17.KR.784811	Revisit	97		08.08.2018 08:54
17.KR.784811		100					08.08.2018 09:19
17.KR.784811		114				09.08.2018 04:50
17.KR.784811		115			09.08.2018 04:50
17.KR.784811	Revisit	116		09.08.2018 04:50
17.KR.784811		122					09.08.2018 07:42
17.KR.784811	Revisit	3327		20.12.2018 07:56
17.KR.784811		3328			20.12.2018 08:02
17.KR.784811		3329				20.12.2018 08:02
17.KR.784811		3351					20.12.2018 11:31
17.KR.784811		3352					20.12.2018 11:39
17.KR.784811	Revisit	3615		09.01.2019 09:14
17.KR.784811		3616				09.01.2019 09:16
17.KR.784811		3617			09.01.2019 09:16
17.KR.784811		3621					09.01.2019 09:40

is this ok?

[Anonymous]

Hi, any ideas?