Transpose? Pivot? Unpivot? Which? How?

Mo.jo.jo · September 4, 2020, 5:32pm

Hello

I have a wide fact table, which is likely to get wider in future, and that I would like to transform into a much much less wide fact table.
The original wide one looks like so:

At the moment I am “cheating” by creating duplicate copies of the table (within Query editor view) to edit and append to achieve the outcome that I would like, which looks like so:

This approach of creating temp tables won’t be sustainable since the columns shown in the original raw data are likely to grow and it does not make sense to keep doing things the way that I am currently doing. There must be a quicker way.
I currently can’t figure out which some combination of transpose/pivot/unpivot to use to achieve the goal within one table (if that is possible).

I hope someone can assist with this.
Power BI file attached.
Power query - structure challenge.pbix (31.1 KB)

Thanks for your time.

BrianJ · September 4, 2020, 5:44pm

@Mo.jo.jo,

This is definitely doable. However, when posting a PQ-related question, please be sure to also post the Excel data file, since PQ wili throw this type of error without it. Thanks!

Brian

Mo.jo.jo · September 4, 2020, 5:46pm

@BrianJ

Hello again Brian

Ok. Noted.

Please find Excel file attached.

Power query - Test data.xlsx (12.4 KB)

Rajesh · September 4, 2020, 6:19pm

Hi @Mo.jo.jo

Try this

Power query - structure challenge.pbix (38.1 KB)

MK3010 · September 4, 2020, 7:04pm

Hi @Mo.jo.jo

@Rajesh has already replied very fast but still i post below code:

let
    Source = Excel.Workbook(File.Contents("C:\Users\vgamidi\Downloads\Power query - Test data.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Table1_Table, {"Business Unit"}, "Attribute", "Goals"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Added Index" = Table.AddIndexColumn(#"Split Column by Delimiter", "Index", 0, 1, Int64.Type),
    #"Added Entries" = Table.AddColumn(#"Added Index", "No of Entries", each #"Added Index"{[Index]+1}[Goals]),
    #"Added Date" = Table.AddColumn(#"Added Entries", "Date", each #"Added Index"{[Index]+2}[Goals]),
    #"Filtered Rows" = Table.SelectRows(#"Added Date", each ([Attribute.2] = null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Business Unit", "Goals", "No of Entries", "Date"})
in
    #"Removed Other Columns"

Melissa · September 4, 2020, 7:14pm

Absolutely love it !!
Just shows how many different solutions are possible in Power Query.

@Rajesh and @MK3010

Seems I started late… since I also have another approach I’m just going to share it as well.
I’ve created a fxTransformTable function that takes a single table as argument.

(myTable as table) =>
let
    TableToColumns = Table.ToColumns(myTable),
    ConvertToTable = Table.FromList(TableToColumns, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    RemoveFirstRows = Table.Skip(ConvertToTable,1),
    AddedIndex = Table.AddIndexColumn(RemoveFirstRows, "Index", 0, 1, Int64.Type),
    UpdatedIndex = Table.AddColumn(AddedIndex, "UpdatedIndex", each if Number.Mod([Index], 3)= 0 then [Index] else null),
    #"Filled Down" = Table.FillDown(UpdatedIndex,{"UpdatedIndex"}),
    AddBusinessUnitList = Table.Group(#"Filled Down", {"UpdatedIndex"}, {{"Count", each {{myTable[Business Unit]}, _[Column1]}, type list}}),
    ExpandL1 = Table.ExpandListColumn(AddBusinessUnitList, "Count"),
    ExpandL2 = Table.ExpandListColumn(ExpandL1, "Count"),
    GroupRows = Table.Group(ExpandL2, {"UpdatedIndex"}, {{"Table", each Table.FromColumns(_[Count]), type table}})
in
    GroupRows

.
When Invoked it creates a series of nested tables.

And when combined with those sideward arrows in the Table column
It doesn’t matter how many columns sets will be added in future, this solution is dynamic.

Here’s your sample file eDNA - Structure challenge.pbix (35.7 KB) .
I hope this is helpful

Mo.jo.jo · September 4, 2020, 7:41pm

@Rajesh

Hello again

Thank you once again for another solution to a problem.
Still not sure how it works but will work through trying to get it.
Many many thanks.

@MK3010 & @Melissa
Thank you both a lot for your solutions and assistance.
Very helpful to see the different approaches used.

@Melissa I was going to complain about how difficult I thought Rajesh’s solution was but then then you did the function thing and then I deleted that comment I was typing.ha ha.

@Rajesh @MK3010 @Melissa
Can any of you suggest any resources to better understand the thinking behind understanding these Pivot, Unpivot situations and how to learn going about them? I don’t get how to even start thinking about how to go about them other than just trial, error and unsuccessful guess work.

BrianJ · September 4, 2020, 7:52pm

@Melissa - well, if that’s not a video request, I don’t know what is… This would be a great example to add to your ongoing series.

@Rajesh, @MK3010 and @Melissa - well done, all!

Brian

Mo.jo.jo · September 4, 2020, 7:58pm

@BrianJ Great shout
Gets my vote

Did not know about the ongoing series.
Though there’s quite a bit of good learning trawling through EDNA videos, are there any links to easily find these?

MK3010 · September 4, 2020, 8:00pm

Hi @Mo.jo.jo

One way to think of is if you have repeated structured column then you try to use this method— you have dates in columns headers.

Melissa · September 4, 2020, 8:03pm

You can find those on the eDNA YouTube Channel
But they have also been added to the Portal you can find them in the Power Query series module

It’s an ongoing project so new video’s in the “Unpivot Series” will continue to be added over time.

Mo.jo.jo · September 4, 2020, 8:09pm

@MK3010
Ok. So it is kind of a pattern to use where your columns follow a set pattern.
Here’s hoping I don’t face a situation where a random naughty column gets added into the mix.ha ha.

@Melissa
Thanks for the link. I missed that within the portal.
Brilliant and very necessary addition. There goes a bit of Saturday

Thank you all and wishing you a very good weekend.

sprmnt21 · May 1, 2021, 5:06pm

I have not read all the answers in detail, but any of these two solutions may be different in some respects from those already presented Power query - structure challenge.pbix (36.0 KB)

Edoited and corrected.
Power query - structure challenge1.pbix (35.7 KB)

sprmnt21 · May 1, 2021, 7:47pm