Forum Discussion
Unpivot Multiple Columns with different formats
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 :))
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
Hi, sorry I forgot to mention that "Issue" field is in text format and the "score" filed are in numbers
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
Date Client Issue_1 Score_1 Issue_2 Score_2 Issue_3 Score_3 Issue_4 Score_4 2020-09-30 A I11 S11 I12 S12 I13 S13 I14 S14 2020-10-01 B I21 S21 I22 S22 I23 S23 I24 S24 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
Thanks a lot for the answer. CNENFRNL
To be honest I've been lost in the Code you've provided 🙂
Can you please provide the steps in Power Quuery wise how to get the desired result. But I think the "Unpivot other columns" does not work.Here is the orginal table (at least the original columns):
Num Process Date Survey_Code Question_1 Score_1 Question_2 Score_2 Question_3 Score_3 კითხვა4 Question_4 Identificator Comment 148 Process_1 01-Jul-19 N148.1.7.19/PD-03 Question_1.1 Score_1.1 Qestion_1.2 Score_1.2 Qestion_1.3 Score_1.3 Qestion_1.4 Score_1.4 2960 Commnet_1 149 Process_2 01-Jul-19 N149.1.7.19/PD-03 Question_2.1 Score_2.1 Qestion_2.2 Score_2.2 Qestion_2.3 Score_2.3 Qestion_2.4 Score_2.4 2838 Commnet_2 150 Process_1 10-Jul-19 N150.1.7.19/PD-03 Question_3.1 Score_3.1 Question_3.2 Score_3.2 Question_3.3 Score_3.3 Question_3.4 Score_3.4 2477 Comment_3 Which I want to be tranferred to the following one:
Num Process Date Survey_Code Identificator Comment Question Score 148 Process_1 01-Jul-19 N148.1.7.19/PD-03 2960 Commnet_1 Question_1.1 Score_1.1 148 Process_1 01-Jul-19 N148.1.7.19/PD-03 2960 Commnet_1 Qestion_1.2 Score_1.2 148 Process_1 01-Jul-19 N148.1.7.19/PD-03 2960 Commnet_1 Qestion_1.3 Score_1.3 148 Process_1 01-Jul-19 N148.1.7.19/PD-03 2960 Commnet_1 Qestion_1.4 Score_1.4 149 Process_2 01-Jul-19 N149.1.7.19/PD-03 2838 Commnet_2 Question_2.1 Score_2.1 149 Process_2 01-Jul-19 N149.1.7.19/PD-03 2838 Commnet_2 Qestion_2.2 Score_2.2 149 Process_2 01-Jul-19 N149.1.7.19/PD-03 2838 Commnet_2 Qestion_2.3 Score_2.3 149 Process_2 01-Jul-19 N149.1.7.19/PD-03 2838 Commnet_2 Qestion_2.4 Score_2.4 150 Process_1 10-Jul-19 N150.1.7.19/PD-03 2477 Comment_3 Question_3.1 Score_3.1 150 Process_1 10-Jul-19 N150.1.7.19/PD-03 2477 Comment_3 Question_3.2 Score_3.2 150 Process_1 10-Jul-19 N150.1.7.19/PD-03 2477 Comment_3 Question_3.3 Score_3.3 150 Process_1 10-Jul-19 N150.1.7.19/PD-03 2477 Comment_3 Question_3.4 Score_3.4 Thanks in advance,
