Data Transform: consolidating same value format

[giordani2000]

Hello team,

Looking for help transforming the data in this file.

It should be a simple excercise but I am missing a couple of steps.

Appreciate you help.

GR.

[giordani2000]

Bingo ....@DOLEARY85 super helpful....Thanks.

[DOLEARY85]

No problem, happy to help 🙂

[giordani2000]

@DOLEARY85 I just ran into some trouble. When I run step 5: Expand the table and only bring through the Category and value columns

Cat1 values for 21.03 (Mar 2021) duplicate when I merge (Table 1 and 2) and Cat2 values for 21.03 (Mar 2021) duplicate when I merge with Table 3. See dup data below in red and blue.

How do I prevent the data from duplicating? Thanks.

[DOLEARY85]

Ah yes, the combination of fields to merge on aren't unique.

Add an Index column from the add column ribbon after the unpivoted columns step on each of the tables and then when merging the tables include the Index as one of the matching columns. This should remove duplicates

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[giordani2000]

Here are the steps I am following:

1. Duplicate the table twice so you have 3 copies.
2. On each one select 3 of the columns for a category (Attribute, Ratio or Multiple) then unpivot them
3. Add an Index column from the add column ribbon after the unpivoted columns step on each of the tables 
4. Delete the other categories on each that you haven't unpivoted on each table and rename the columns in attribute and value with an appropriate name and number e.g Category 1 & Value 1, Category 2 & Value 2 etc
5. Return to main table and use merge Table 1 and 2 creating a new Query. Merge new query with Table 3. When mergin select Widgets, Qtr, Maturity Qtr and Index as matching columns on both.
6. Expand the table and only bring through the Category and value columns 

I get a bunch of null when I expand  the results from merging the New Query and table 3. I believe the reason is that Table 1 and 2 has 4 attiributes but Table 3 has 3 attribute only. So, for example the index does not correspond to the same quarters when comparing Table 3 with Table 1 and 2. Should adding a step to create a dummy attribute work? or is there a different solution?

[giordani2000]

@DOLEARY85 here is a file for reference 

[DOLEARY85]

Thanks, okay i see the problem, Attribute & Multiple join fine but ratio only has 2 columns so it's not going to create the same amount of rows when unpivotted. There needs to be a unique field created after unpivoting to join the rows back.

I'll look at this today and see if i can come up with something.

[DOLEARY85]

I've updated the file with something that should work, take a look and let me know.

There will be blanks in the Ratio column as you only have 2 columns instead of 3 as with the other attribute & multiple.

Hope this helps

[Jakinta]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZJLjsMgEESvElnjXdSif3yOkdUsIt/Dxx9o2piQ2VSBZb8uyrzf2y9uz40QXlw94Kvq4wcZNDxsmSCnvqIAbA8DlLMaQjhtp7aTvak2lbRvx3NiS3uPOptxsBUCOVshOjud94gAYmy62awzmzy3dnZOQNqJxYE28MqdOzR2w2acjBqbqixsyx07WxCifLHVD7N2Um52tGbkYss/fWsNM9jqJ5A7t3fixs1KtsRd48Ke+04I6GnjmCIE9Mn2TqhZtNzJOhGc2UvfqX6HnZhB/F8KD7b3nad7wsHYlhvLwp77Lgws1z0ZnQjkT7ZMI9QSI+/ez3H8AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Widgets = _t, Qtr = _t, #"Maturity Qtr" = _t, Attribute1 = _t, Attribute2 = _t, Attribute3 = _t, Multiple1 = _t, Multiple2 = _t, Multiple3 = _t, Ratio1 = _t, Ratio2 = _t, Ratio3 = _t]),
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Widgets", "Qtr", "Maturity Qtr"}, "Attribute", "Value"),
    Attribute = List.Transform (List.Select(Table.ToRecords(Unpivoted), each Text.Contains([Attribute],"Attribute")), Record.ToList),
    Multiple = List.Transform( List.Select(Table.ToRecords(Unpivoted), each Text.Contains([Attribute],"Multiple")), each Record.ToList(Record.SelectFields(_,{"Attribute","Value"})  )),
    Ratio = List.Transform( List.Select(Table.ToRecords(Unpivoted), each Text.Contains([Attribute],"Ratio")), each Record.ToList(Record.SelectFields(_,{"Attribute","Value"})  )),
    Combined = Table.FromRows ( List.Transform(List.Zip( {Attribute,Multiple,Ratio}), List.Combine), 
List.FirstN(Table.ColumnNames(Source),3)&{"Category","Value$","Category2","ValueX","Category3","Value%"}),
    #"Added Index" = Table.AddIndexColumn(Combined, "Index", 0, 1, Int64.Type),
    Sorted = Table.Sort(#"Added Index",{{"Category", Order.Ascending}, {"Index", Order.Ascending}}),
    FINAL = Table.RemoveColumns(Sorted,{"Index"})
in
    FINAL

You can try this as well.

[giordani2000]

Thanks Jakinta. What the best way to implement this code? is it a copy/paste somewhere in power query OR do I need to follow step by step and excecute each step in power query? Thanks.

[DOLEARY85 · Accepted solution]

Hi,

In power query:

1. Duplicate the table twice so you have 3 copies.
2. On each one select 3 of the columns for a catagory (Attribute, Ratio or Multiple) then unpivot them
3. Delete the other categories on each that you haven't unpivoted on each table and rename the columns in attribute and value with an appropriate name and number e.g Category 1 & Value 1, Category 2 & Value 2 etc
4. Return to you main table and use merge queries to combine the other 2 tables selecting Widgets, Qtr & Maturity Qtr as matching columns on both
5. Expand the table and only bring through the Category and value columns 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍