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 CodeNameCapacityAppointee CodeAppointing Code
AG1234ABCAppointing Party AB98765
AG1234XYZAppointeeAB23546 
AG996QWEAppointing Party AB67456
AG996ASDAppointeeAB87430 
AG997POIAppointing Party AB98765
AG997CABAppointeeAB90756 

 

 

to the following:

 

AG CodeAppointee CodeAppointee NameAppointing CodeAppointing Name
AG1234AB23546XYZAB98765ABC
AG996AB87430ASDAB98765QWE
AG997AB90756CABAB98765POI
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

3 Replies

  • 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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    This M code works

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQ3NDI2UdJRcnRyBpEFBfmZeSWZeekKAYlFJZVAIbCcpYW5malSrA6ShojIKISG1FSwMiNjUxMzkB6ISktLECcw3BWPyWbmJqZmKOodg10wDLYwNzE2QDbYHMgJ8Pck0skQ9c6OThgGWxqYm0JcHAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"AG Code" = _t, Name = _t, Capacity = _t, #"Appointee Code" = _t, #"Appointing Code" = _t]),
    #"Merged Columns" = Table.CombineColumns(Source,{"Appointee Code", "Appointing Code"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Code"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Name", "Code"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns1", List.Distinct(#"Merged Columns1"[Capacity]), "Capacity", "Merged"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Pivoted Column", "Appointing Party", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Appointing name", "Appointing code"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Appointee", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Appointee name", "Appointee code"})
in
    #"Split Column by Delimiter1"

    Hope this helps.