Transpose Data in Power Query Problem

[han_rj]

Hi ,

I need help achieve this expected output where the resultant is a transposed data. Row scrore is transposed into a column but instead of manually renaming as Country1_Score is there an automated way and automatically create the sheet name as another column

[dufoq3 · Accepted solution]

Hi @han_rj, check this:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8swrKC1R0lFCoFidaCjTOb80r6SoUsEQVSY4tbg4Mz/PEME0QjCNEUwTsIbg5PyiVKCgoQGQMAURRmAWWNI3NSUxByQExMZgUaBCJBfgdJIRNZxkCrXZDKt7DKBuAronFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    // You can probably delete this step.
    ReplacedBlankWithNull = Table.TransformColumns(Source,{}, each if _ = "" then null else _),
    FilteredRows = Table.SelectRows(ReplacedBlankWithNull, each not List.IsEmpty(List.RemoveNulls(Record.ToList(_)))),
    FnCheckCountry = each List.Contains(Record.ToList(_), "Country", (x,y)=> Text.StartsWith(x ?? "", y)),
    RemovedTopRows = Table.Skip(FilteredRows, each not FnCheckCountry(_)),
    Ad_GroupHelper = Table.AddColumn(RemovedTopRows, "GroupHelper", each FnCheckCountry(_), type logical),
    GroupedRows = Table.Group(Ad_GroupHelper, "GroupHelper", {{"T", each
        [ a = Table.RemoveColumns(_, {"GroupHelper"}),
          b = Table.PromoteHeaders(Table.FromRows(Table.ToColumns(Table.Skip(a)))),
          c = List.Transform(Table.ColumnNames(b), (x)=> Text.Combine({List.RemoveNulls(Record.ToList(a{0})){0}, x}, "_")),
          d = Table.RenameColumns(b, List.Zip({ Table.ColumnNames(b), c }))
        ][d], type table}}, 0,
        (x,y)=> Byte.From(y = true) ),
    T = GroupedRows[T],
    Combined = Table.FromColumns(List.Combine(List.Transform(T, Table.ToColumns)), List.Combine(List.Transform(T, Table.ColumnNames)) ),
    RenamedColumns = Table.RenameColumns(Combined,{{Table.ColumnNames(Combined){0}, "Type"}}),
    RemovedOtherColumns = Table.SelectColumns(RenamedColumns, List.Select(Table.ColumnNames(RenamedColumns), (x)=> not Text.EndsWith(x, "Column1")))
in
    RemovedOtherColumns

[ToddChitt]

The steps to get to your output will be a bit involved. Hope you can follow my descripiton here:

Start by adding a calculated column (let's call this Column 6) and setting it to Column 3 IF the contents of Column 1, 2, 4 or 5 are NULL. 

Do a Fill Down to propagate that value to the rows below.

Assign column names manually: "Type", Session1, 2, 3, 4, and Country

Filter out where the first column (Type, or whatevery you name it) is null. You should now have a dataset of four rows and 6 columns.

Now experiment with various PIVOT and UNPIVOT operations.

Hope that helps