Forum Discussion

George1973's avatar
George1973
Helper V
5 years ago
Solved

Unpivot Multiple Columns with different formats

Hi All,

I'm looking for the easy solution with Power Query for the following task:
I have a sample table like given below:

..And I would like to unpivot the "Issues" ans "Score" columns as given in the second picture:

Thanks in advance,

Need Help
  • George1973's avatar
    George1973
    5 years ago

    Hi,
    v-xuding-msft CNENFRNL Jimmy801 AlB 
    Thanks a lot for your aissists,
    I have solved the issue in my "artificial" way:
    I have imported the same table four times and edited them according the need:
    Fist table - I have left there the basic columns and then first columns for 1st question and 1st score.
    then I have did the same with the second table leaving there basics and columns for 2nd questions ans scores and so on..
    Then I have just UNION all four tables :)))))
    That's all :))

12 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Hi George1973 

    Can you please show your data in text-tabular format in addition to (or instead of) the screen captures? Particularly the first table.  A screen cap doesn't allow people to readily copy the data and run a quick test.  Just use 'Copy table' in Power BI and paste it here.

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    Hello George1973 

     

    choose the first two columns, and select the function "Unpivot other columns"


    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

    • George1973's avatar
      George1973
      Helper V
      5 years ago

      Hi, sorry I forgot to mention that "Issue" field is in text format and the "score" filed are in numbers

      • CNENFRNL's avatar
        CNENFRNL
        Community Champion
        5 years ago

        Hi, George1973 , I'm afraid you misunderstood what AlB means. It's way much easier for others to propose solutions to your issues if you paste a sample table like this; the formats of specific columns make no difference.

        Sample

        DateClientIssue_1Score_1Issue_2Score_2Issue_3Score_3Issue_4Score_4
        2020-09-30AI11S11I12S12I13S13I14S14
        2020-10-01BI21S21I22S22I23S23I24S24

        As to solution, you may try,

         

         

         

         

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LYwxCsAwDMT+4jkB+5KlY7t57hjy/2/UER0sBIe1lsnl3a8+3JrddRlRfGGGcOEDH/jEp+32J6Iq5+U5s0jAFAmYIgFTJIp7fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Client = _t, Issue_1 = _t, Score_1 = _t, Issue_2 = _t, Score_2 = _t, Issue_3 = _t, Score_3 = _t, Issue_4 = _t, Score_4 = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Date", "Client"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Date", "Client", "Index"}, {{"Grouped", each Record.FromList(_[Value], {"Issue", "Score"})}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows",{"Index"}),
    #"Expanded Grouped" = Table.ExpandRecordColumn(#"Removed Columns1", "Grouped", {"Issue", "Score"}, {"Issue", "Score"})
in
    #"Expanded Grouped"

         

         

         

         

         

        Result