Forum Discussion

DanFromMontreal's avatar
DanFromMontreal
Helper IV
4 years ago
Solved

Creating a table like a pivot table

Hello dear Power Query Community, I'm fairly new at using PowerQuery and what a wonderful tool at transforming data. I want to create, using PowerQuery, a table that returns the result shown on the...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Since, you don't want to go through a pivot table, you can download following mock-up solution.

     

    https://1drv.ms/x/s!Akd5y6ruJhvhuRVRl0cdM3wUZaN6?e=sEB3dt

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Account"}, {{"Temp", each _, type table [Account=text, #"HQ-NHQ"=text]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),
    #"Expanded Temp" = Table.ExpandTableColumn(#"Added Index", "Temp", {"HQ-NHQ"}, {"HQ-NHQ"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Expanded Temp", "Index", "Index - Copy"),
    #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[#"HQ-NHQ"]), "HQ-NHQ", "Index", List.Count),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index - Copy", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index - Copy"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Duplicate HQ and NHQ", each if [HQ]>0 and [NHQ]>0 then "Duplicate" else "Unique"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Duplicate NHQ", each if [NHQ]>1 then "Duplicate" else "Unique"),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"HQ", "NHQ"})
in
    #"Removed Columns1"

     

DanFromMontreal's avatar
DanFromMontreal
Helper IV
4 years ago

Anonymous , unfortunately, I cannot read your attachement (pbix).  I'm using Power Quey (Excel) and I do not have Power BI installed on my computer and due to company's restrictions, I cannot install it.

Would it be possible the copy/paste your solution like Vijay did in his reply?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    here it is

    let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYqVYnWglJ2SOM5BhBBYA8VygUkZgnitIEUzKDcKByLhDdQF5sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [account = _t, hq = _t, nhq = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"account", type text}, {"hq", Int64.Type}, {"nhq", Int64.Type}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "DUP_HQ", each if [hq]<>null and [nhq]<>null then "D" else "U"),
    #"Aggiunta colonna personalizzata1" = Table.AddColumn(#"Aggiunta colonna personalizzata", "DUP_NHQ", each if ([nhq] ?? 0) > 1 then"D" else "U")
in
    #"Aggiunta colonna personalizzata1"
    • DanFromMontreal's avatar
      DanFromMontreal
      Helper IV
      4 years ago

      Thank you Rocco but I do not understand the Source ansd the Json.Document.

      My source is a table within my Excel document.  Would it be possible to convert your code to suit my needs?  Again, I'm a beginner in PowerM language.

      Source = Excel.CurrentWorkbook(){[Name="tblConsolidé"]}[Content],

       

      Grazie