Creating a table like a pivot table

[DanFromMontreal]

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.

[DanFromMontreal]

Thank you for the clarification.

So much to learn....

I will try

[Anonymous]

try this

[DanFromMontreal]

@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]

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]

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

[Vijay_A_Verma · Accepted solution]

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]

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,

[BA_Pete]

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]

@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]

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