Forum Discussion

Malsk1_1's avatar
Malsk1_1
Icon for Helper II rankHelper II
3 years ago
Solved

How to flatten data in Power BI

Hi,    Example Data  How can i flatten the data in the attached excel to the desired output:   AG Code Name Capacity Appointee Code Appointing Code AG1234 ABC Appointing Party   AB...
Need Help
  • Malsk1_1's avatar
    Malsk1_1
    3 years ago

     

    Any ideas?  I have tried the options to pivot/unpivot but couldnt get to the desired output

Malsk1_1's avatar
Malsk1_1
Icon for Helper II rankHelper II
3 years ago

 

Any ideas?  I have tried the options to pivot/unpivot but couldnt get to the desired output

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, Malsk1_1 

     

    You can try the following methods.
    Column:

    New Appointee Code = CALCULATE(MAX('Table'[Appointee Code]),ALLEXCEPT('Table','Table'[AG Code]))
    New Appointing Code = CALCULATE(MAX('Table'[Appointing Code]),ALLEXCEPT('Table','Table'[AG Code]))

    Table:

    New table = 
Var _table=SUMMARIZE('Table','Table'[AG Code],'Table'[New Appointee Code],'Table'[New Appointing Code],'Table'[Name],'Table'[Capacity])
Return
FILTER(_table,[Capacity]="Appointing Party")

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.