Making data table 2 from data table 1. Please help

[MizanurManik]

I have table 1 data I want tomake table to in power BI desktop. I can I do that 

[olgad]

Hi, 
first vreate a column without the AD_AA_

Then Home, click New Table and create Summarize

Please kudo and accept the solution if it works for you. 

[MizanurManik]

Thanks If all data is not 5 caracter length than what formula should I apply? 

and one more question caracter count before _ (like AD_ become different length ADC_) what should I do? 

[olgad · Accepted solution]

Provided the delimiter stays the same, split the column by delimiter in power query. dont create one in dax.