Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Stack and Pivot?

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 ...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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.

wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
5 years ago

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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 :-)!