Stack and Pivot?

[Anonymous]

I am ingesting and re-arranging an automated report for the purpose making certain fields filterable and sortable. I have done the Power Query transforming to get it from its original EXTREMELY ugly state into this (simplified, but representative) state:

The objective is to get all of the questions across the top as a header so that all companies answers are filterable/sortable/etc. My Power Query skills, or even vocabulary, are stuck on this. Some combination of pivoting, unpivoting, transposing. The output would look like this:

As an aside, this repeating-in-two-directions situation was created because the ingested report contains many sheets. If there is a different/better way to bring in multiple sheets, I am all ears.

Thanks!

[v-jingzhang · Accepted solution]

Hi @Anonymous

It is possible to transform the data into the output you want. Create a blank query and paste below codes into its Advanced editor to see the steps. Or download the attachment at the bottom.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdGtDsJAEEXhVyGra9g/XzozHt1UICoQbAmFEN4eBdnNEWuv+MQ98+zOr3V/XrdyOLrBTdvtfimfcSz7e31UywnL9FuWoUI8EA/EA/EtEoAEIAFIaJEIJAKJQGKLJCAJSAKSWiQDyUAykNwi1fOCForFenUELRSL9eoIWigW69URtFAs1qsjaKFYrFdH0EKx2L/O8gU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"AllContent", each _, type table [Column1=nullable text, Column2=nullable text, Column3=nullable text, Column4=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Combine({[AllContent][Column2], [AllContent][Column3], [AllContent][Column4]})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllContent"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6"}),
    #"Transposed Table" = Table.Transpose(#"Split Column by Delimiter"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Anonymous]

Any other ideas or thoughts on this one?

[wdx223_Daniel]

NewStep=let a=Table.Group(PreviousStep,"Column1",{"n",each List.Combine(List.Skip(Table.ToColumns(_)))}) in Table.FromColumns(a[n],a[Column1])

[Anonymous]

I couldn't get that query to work without errors (even after changing the variable names and such to fit). But your Advanced Editor syntax helped me discover what might be a different step forward.

Here is my thought process. Can I use a combination of:

Table.SelectColumns - To grab a combination of relevant columns (basically a question and answer pair)

Table.ToRows - To turn that selection into rows

Table.InsertRows - To stack that set of rows under or over the existing table

I could repeat that for each of the answer columns and end up with one long pair of columns for questions and answers... which I could then pivot to get to my final state.

I am trying this now but I think the dimensionality of my row insert is off (basically Table.ToRows doesn't seem to be feeding Table.InsertRows input that is the right shape). Perhaps the nested lists coming out of Table.ToRows are getting squirrely?

Here is the line: 

= Table.InsertRows(#"Filtered Rows1",0,{Table.ToRows(Table.SelectColumns(#"Filtered Rows1",{"Custom.Name","Custom.Data.Column2","Merged2"}))}) 

This is the error:

Expression.Error: We cannot convert a value of type List to type Record.
Details:
Value=[List]
Type=[Type]

Does this approach make sense? Where is my mistake? I have Venmo :-)!

[mahoneypat]

You should consider putting the Company names as the names of columns 2-4 and then unpivoting them, so your table has Company, question, and answer columns.  This will set you up for easier analysis/visualization (e.g., put Company column as columns in a matrix visual).

Pat

[Anonymous]

Company Name IS Question1 so Answer1 is the company name in all of these cases.

The challenge with titling that way (if I am understanding your suggestion correctly) is that column 2 contains CompanyA answers (in rows 1-6), and CompanyD answers (in 7-12), and CompanyG answers (though I have removed those for the sake of clarity here, in 13-18). That will probably make more sense if you look closely at the arrangement of the questions and the arrangement of the answers for companies A-F.

The pattern repeats downward a hundred or so times. The pattern is currently three companies wide, but will eventually be 5 or so companies wide (because of the ridiculous way this report is originally formatted).

Does that make any sense?

[wdx223_Daniel]

NewStep=Table.PromoteHeaders(Table.Transpose(PreviousStepName))

[Anonymous]

Transposing alone won't get me there, I don't think. That Question1-6 pattern repeats a hundred+ times, so transposing leaves me with a thousand+ columns and still with three rows of answers.

It feels like the approach I need will be to:

-Get (from my starting example) C1:C12 and D1:D12 to populate underneath the stack (which in this example would be B13 and down). Then I could pivot.

OR

-Get (from my starting example) B7:D12 over next to the rest (in this case, E1). Then I could transpose.

Where I am stuck is how to get these groupings of answers moved to go along one dimension or the other. Does that make sense?