Avoid duplication in quantities, but not in group value; best to show in example

[Anonymous]

Hi there,

I've got an interesting challenge where I'd like some help with:

my source table is formatted as such:

current source table

so, any ID can have 1 or multiple values, but each ID can only have 1 quantity. the source table is formatted this way to avoid double counting of quantities, while still enabling capturing each potential attribute value. (a-d)

to work with in power however, it's not ideal to work with the formatting as this. Easier would be to have these values in 1 column ("attributes").

Doing this in power query, would work well, yet it would multiply the quantities depending on the number of attributes present per ID. As per below:

after 'unpivoting'

What I'm trying to find a solution for is how to end up with all the ID's and all their respective attributes captured, yet without double counting quantities. (for example in column Q*)

suggestions are welcome! thanks a lot

[CheenuSing · Accepted solution]

Hi @Anonymous ,

I created the unpivoted  table using enter data.

Then using Query Editor --> Advanced Editor I pasted the following query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIF4kSlWB0ELwXMSwKyzOByyUCWsQmQSELlJqNyU8HcFCDL0ABiUiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Quantity = _t, Attribute = _t]),
// Replace this Source with your original source //   

#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Quantity", Int64.Type}, {"Attribute", type text}}),

    Partition = Table.Group(#"Changed Type", {"ID"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"ID", "Quantity", "Attribute", "Index"}, {"ID.1", "Quantity", "Attribute", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Partition",{"ID.1"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Q*", each if [Index] =1 then [Quantity] else 0)

in
    #"Added Custom"

When you navigate through each step you will see it working.

The output after the above steps in query editor

   

Cheers

CheenuSing

[CheenuSing · Accepted solution]

Hi @Anonymous ,

I created the unpivoted  table using enter data.

Then using Query Editor --> Advanced Editor I pasted the following query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIF4kSlWB0ELwXMSwKyzOByyUCWsQmQSELlJqNyU8HcFCDL0ABiUiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Quantity = _t, Attribute = _t]),
// Replace this Source with your original source //   

#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Quantity", Int64.Type}, {"Attribute", type text}}),

    Partition = Table.Group(#"Changed Type", {"ID"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"ID", "Quantity", "Attribute", "Index"}, {"ID.1", "Quantity", "Attribute", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Partition",{"ID.1"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Q*", each if [Index] =1 then [Quantity] else 0)

in
    #"Added Custom"

When you navigate through each step you will see it working.

The output after the above steps in query editor

   

Cheers

CheenuSing

[Anonymous]

Dear @CheenuSing ,

thank you for your feedback and when I follow the same process - i.e. create the unpivoted table manually - I get the desired result and I think I understand (most) of the steps. 

When I'm now applying the logic to my source table, I'm getting an error message saying that the first column name in my table is not recognised in the "Change Type" step. The first column in my source table is not the equivalent of the ID I had used in my example. Should the tranformed table have the ID as the first column? 

thanks,

[CheenuSing · Accepted solution]

Hi @Anonymous ,

Can you share the data model of your file. What is the equivalent of ID in your table?

Use that for indexing.

Cheers

CheenuSing