Unpivot Multiple Columns with different formats

[George1973]

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,

[Jimmy801]

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]

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

[CNENFRNL]

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

[George1973]

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,

[CNENFRNL]

Hi, @George1973 , M code seems indeed intimidating at first glance. As a matter of fact, the very essence of my solution is the step #"Integer-Divided Column", which bins the table by every two rows (question/score pair) and then group the table by those pairs.

If it's hard to digest all at once, no worries, here's another solution easy to chew (100% via UI)😁

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddC9CoMwGIXhe8msab4vsZq5nToUpaOIg2QoVAP+3H9jLeXE4uTBN4SH1LUgU4hElKPv3DS1FLai9La8UrJh30OWJHNJ9lReU6XDv2px0/z0Q0tyPf7o/Oi+u/oVhsJR0VB0VAyUdbM9q/C5+L4f3BxsTbJ6LXj532sPvQxejrwMXo68DF6OvAzebXOhC/Dy5s3U7n1JoTdTh14N3m1DYkgcJw1pd6GB9CGbPP+S3TC3WjTNGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Num = _t, Process = _t, Date = _t, Survey_Code = _t, Question_1 = _t, Score_1 = _t, Question_2 = _t, Score_2 = _t, Question_3 = _t, Score_3 = _t, Question_4 = _t, Score_4 = _t, Identificator = _t, Comment = _t]),

    // Merge question/score columns in pairs
    #"Merged Columns" = Table.CombineColumns(Source,{"Question_1", "Score_1"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged1"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Question_2", "Score_2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged2"),
    #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Question_3", "Score_3"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged3"),
    #"Merged Columns3" = Table.CombineColumns(#"Merged Columns2",{"Question_4", "Score_4"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged4"),
    
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns3", {"Num", "Process", "Date", "Survey_Code", "Identificator", "Comment"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Value", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Question", "Score"})
in
    #"Split Column by Delimiter"

[George1973]

@CNENFRNL 

Good evening,
Unfortunately I failed to copy-paste the M-code you've provided.

The reason is - There is previous duery changes in my table and I could not append the M-code with the previous writings.

Here is the existing M-Code:

let
    Source = Excel.Workbook(File.Contents("C:\Users\jokhadze\Documents\KAIZEN\PROJECTS\CONTRACTED\NEOTECH\New Contract - 2021\F-MI-01D-01 Process reporting_ KPI allMd(Copy).xlsx"), null, true),
    #"Call Center_Sheet" = Source{[Item="Call Center",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"Call Center_Sheet",{{"Column1", Int64.Type}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}, {"Column5", type text}, {"Column6", type text}, {"Column7", Int64.Type}, {"Column8", type text}, {"Column9", Int64.Type}, {"Column10", type text}, {"Column11", Int64.Type}, {"Column12", type text}, {"Column13", Int64.Type}, {"Column14", type any}, {"Column15", type text}, {"Column16", type any}, {"Column17", type text}, {"Column18", type any}, {"Column19", type text}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1"}),
    #"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"N", Int64.Type}, {"პროცესი", type text}, {"გამოკითხვის თარიღი", type any}, {"გამოკიტხვის კოდი", type text}, {"კითხვა1", type text}, {"Column6", Int64.Type}, {"კითხვა2", type text}, {"Column8", Int64.Type}, {"კითხვა3", type text}, {"Column10", Int64.Type}, {"კითხვა4", type text}, {"Column12", Int64.Type}, {"ჯამი", Int64.Type}, {"შედეგი", type text}, {"იდენტიფიკატორი", type any}, {"კომენტარი", type text}, {"Column17", type any}, {"საანგარიშო პერიოდი", type text}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"N", "Num"}, {"Column6", "Score_1"}, {"Column8", "Score_2"}, {"Column10", "Score_3"}, {"Column12", "Score_4"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"ჯამი", "შედეგი", "Column17", "საანგარიშო პერიოდი", "Column19", "Column20", "Column21"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each ([გამოკითხვის თარიღი] <> "27.02.2020.")),
    #"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows",{{"გამოკითხვის თარიღი", type date}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type2", each ([გამოკითხვის თარიღი] <> null)),
    #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows1",{{"პროცესი", "Process"}, {"გამოკითხვის თარიღი", "Survey_Date"}, {"გამოკიტხვის კოდი", "Survey_Code"}, {"კითხვა1", "Question_1"}, {"კითხვა2", "Question_2"}, {"კითხვა3", "Question_3"}, {"კითხვა4", "Question_4"}, {"იდენტიფიკატორი", "Reference"}, {"კომენტარი", "Comment"}})
in
    #"Renamed Columns1"

Please advise where I should paste your code?

P.S. Some namings were in Georgian language and I changed them to correspond the previous namings I was providing in my posts. Please do not get confused

[Jimmy801]

Hello @George1973 

here another approach. But FYI... this kind of transformation is almost not possible to do with the GUI alone. So you have to dive into M-language. You can replace the first two steps with your data source

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddC9CoMwGIXhe8msab4vsZq5nToUpaOIg2QoVAP+3H9jLeXE4uTBN4SH1LUgU4hElKPv3DS1FLai9La8UrJh30OWJHNJ9lReU6XDv2px0/z0Q0tyPf7o/Oi+u/oVhsJR0VB0VAyUdbM9q/C5+L4f3BxsTbJ6LXj532sPvQxejrwMXo68DF6OvAzebXOhC/Dy5s3U7n1JoTdTh14N3m1DYkgcJw1pd6GB9CGbPP+S3TC3WjTNGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Num = _t, Process = _t, Date = _t, Survey_Code = _t, Question_1 = _t, Score_1 = _t, Question_2 = _t, Score_2 = _t, Question_3 = _t, Score_3 = _t, კითხვა4 = _t, Question_4 = _t, Identificator = _t, Comment = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Num", Int64.Type}, {"Process", type text}, {"Date", type date}, {"Survey_Code", type text}, {"Question_1", type text}, {"Score_1", type text}, {"Question_2", type text}, {"Score_2", type text}, {"Question_3", type text}, {"Score_3", type text}, {"კითხვა4", type text}, {"Question_4", type text}, {"Identificator", Int64.Type}, {"Comment", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Question_4", "Score_4"}, {"კითხვა4", "Question_4"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Num", "Process", "Date", "Survey_Code", "Identificator", "Comment"}, "Attribute", "Value"),
    TransformAttribute = Table.TransformColumns
    (
        #"Unpivoted Other Columns",
        {
            {
                "Attribute",
                each if Text.StartsWith(_, "Score") then "Score" else "Question"
            }
        }
    ),
    #"Pivoted Column" = Table.Pivot(TransformAttribute, List.Distinct(TransformAttribute[Attribute]), "Attribute", "Value", each _),
    Add = Table.AddColumn
    (
        #"Pivoted Column",
        "Table",
        each Table.FromColumns({[Question], [Score]}, {"Question", "Score"})
    ),
    #"Removed Columns" = Table.RemoveColumns(Add,{"Question", "Score"}),
    #"Expanded Table" = Table.ExpandTableColumn(#"Removed Columns", "Table", {"Question", "Score"}, {"Question", "Score"})
in
    #"Expanded Table"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

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]

@Jimmy801 @AlB 
Hi,
Thanks for your responds.
Unfortunately unpivot other colums does not work.
I have also tried to insert M-Code provided by another your collegue, but I failed because I have already some previous actions done in the query editor and that's why the advance aditor was not empty. So, I could not find a proper place where to insert a new M-code to append to the old one.
(Please find our previous replies)

[v-xuding-msft]

Hi @George1973 ,

You could copy their code into a blank query to take a look at the steps. And then apply them to your own query.

[George1973 · Accepted solution]

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 :))

[George1973]

WoW! Looks and sounds very promising 🙂

I will try and let you know later on

[AlB]

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