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"

     

BA_Pete's avatar
BA_Pete
Super User
4 years ago

Hi DanFromMontreal ,

 

Try just adding two new columns in PQ, like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKjrdU0lEyBGKlWB2IgKEBiAcShImYwdQghEyMoKqM4EKWQAAWAAvGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [account = _t, HQ = _t, NHQ = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"account", type text}, {"HQ", Int64.Type}, {"NHQ", Int64.Type}}),
    addDupeHQNHQ = Table.AddColumn(chgTypes, "Duplicate HQ and NHQ", each if ([HQ] ?? 0) > 0 and ([NHQ] ?? 0) > 0 then "Duplicate" else "Unique"),
    addDupeNHQ = Table.AddColumn(addDupeHQNHQ, "Duplicate NHQ", each if ([NHQ] ?? 0) > 1 then "Duplicate" else "Unique")
in
    addDupeNHQ

 

This gives me the following output:

 

Pete

  • DanFromMontreal's avatar
    DanFromMontreal
    Helper IV
    4 years ago

    BA_Pete ,

    I reviewed your solution but my level of knowledge of PowerQuery is limitied.

    I don't really understand how to convert your "Source = table...." step to suit my needs.

    I was able to understand Vijay's "Source = Excel.CurrentWorkbook(){[Name="tblData"]}[Content]" line.

    How can I convert your code to use Vijay's solution?

    • BA_Pete's avatar
      BA_Pete
      Super User
      4 years ago

      Hi DanFromMontreal ,

       

      My Source step is just a JSON representation of example data so you can copy and paste the code into Power BI and it will work for you.

      To use my code on your data, you would just connect a new query to your data source, then paste my code from the chgTypes step onward onto the bottom, remembering to adjust the previous step to your previous step, as the previous step may not be called 'Source' on your data:

      You will probably also need to change the column names referred to in my code to match what they are called in your actual data, but this should hopefully be fairly clear from the code.

       

      Pete