Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Unpivot table with different fields

Hello everybody,   I have searched and read some posts about pivot and unpivot tables; however, my data is a bit differnet and very ugly, I need some help on the this:   Table I got: ID Mont...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago
This is a job for ImkeF or edhans or someone of similar skill. I think it will involve an index column and a custom column that uses like MOD 3 or something and then you group on it. Not sure but those 2 will be able to help.
  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi @trevorhh9 ,

    please paste this code into the advaned editor of a new query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIyMDIwMAIyQlITc6GUQgSMEQlkKMXqoCkNTs4vSgXSpiCsZ45VjW9mXmlJajGQZWYAIkxhqpJgqgzRLI0yhLOM4CxjTD0w2y1AxoKwnhGmIjTrTU0hLPwOMIGzTOEsMzwO0APxTPRADjHH5wSw7eYgd1gAnRALAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, MonthYear = _t, #"Team/Score/Minutes" = _t, #"Value 1 " = _t, #"Value 2" = _t, #"Value 3" = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 3), Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"ID", "MonthYear", "Index"}, {{"Partition", each Table.PromoteHeaders(Table.Transpose(Table.RemoveColumns(_, {"ID", "MonthYear", "Index"})))}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Index"}),
    #"Expanded Partition" = Table.ExpandTableColumn(#"Removed Columns", "Partition", {"Team", "Score", "Minutes"}, {"Team", "Score", "Minutes"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Partition", each ([Team] <> ""))
in
    #"Filtered Rows"

    It automatically wraps to more value columns.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello ImkeF ,

       

      Thanks for the reply.

      I tried to use the query you provided in power bi with the real data. When I did the last step to "#Expanded Partition", it gave the null value for all rows.  It seems like I didn't use the column [Team/Scores/Munites] and maybe PBI didn't know what value to put back.

       

      Or am I doing something wrong?

       

       

      • ImkeF's avatar
        ImkeF
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi Anonymous 

        Yes, you might have to replace the column names in the code.

        You can also delete the last steps and do the expansion manually so you can see what's actually there.