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
Community 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
Community 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
      Community 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.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi ImkeF ,

         

        I found the issue that the column "Team/Scores/Munites" in my read data is not in the third column, so when I expand the table, it showed null as the order is wrong.

         

        Thank you for the brilliant answer!