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 right hand side.

Right now, it is a two (2)  step process to achieve this:

   1)  Analyze the data using a pivot table to group by "count" all records in the field "HQ-NHQ" 

   2)  Create a table with formulas to convert the numeric results into "Duplicate - Unique" (see logic above table)  

After that, I reintroduce in a new field those results using Power Query to my main table (more then 10000 records) using the Table.NestedJoin..." function

It is working but... not optimized

How can I create the right table without having to go through a pivot table and using PowerQuery (M language).

Thank you for your support.


 

 

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"

     

10 Replies

  • 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

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    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

      Thank you so much Vijay_A_Verma , simple but effective.  Working great.

      I need to better understand each step of the process as a learning experience so I can replicate in the future.

      I will also review  what Anonymous  and BA_Pete have suggested.

      Often, they're are multiple ways to achieve a goal.

      Regards,

       

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