Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
George1973
Helper V
Helper V

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:

George1973_0-1601537806866.png

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

George1973_1-1601537959172.png

Thanks in advance,

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

View solution in original post

12 REPLIES 12
Jimmy801
Community Champion
Community Champion

Hello @George1973 

 

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

Jimmy801_0-1601538533450.png


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

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

Screenshot 2020-10-01 113154.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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

NumProcessDateSurvey_CodeQuestion_1Score_1Question_2Score_2Question_3Score_3კითხვა4Question_4IdentificatorComment
148Process_101-Jul-19N148.1.7.19/PD-03Question_1.1Score_1.1Qestion_1.2Score_1.2Qestion_1.3Score_1.3Qestion_1.4Score_1.42960Commnet_1
149Process_201-Jul-19N149.1.7.19/PD-03Question_2.1Score_2.1Qestion_2.2Score_2.2Qestion_2.3Score_2.3Qestion_2.4Score_2.42838Commnet_2
150Process_110-Jul-19N150.1.7.19/PD-03Question_3.1Score_3.1Question_3.2Score_3.2Question_3.3Score_3.3Question_3.4Score_3.42477Comment_3

 

Which I want to be tranferred to the following one:

NumProcessDateSurvey_CodeIdentificatorCommentQuestionScore
148Process_101-Jul-19N148.1.7.19/PD-032960Commnet_1Question_1.1Score_1.1
148Process_101-Jul-19N148.1.7.19/PD-032960Commnet_1Qestion_1.2Score_1.2
148Process_101-Jul-19N148.1.7.19/PD-032960Commnet_1Qestion_1.3Score_1.3
148Process_101-Jul-19N148.1.7.19/PD-032960Commnet_1Qestion_1.4Score_1.4
149Process_201-Jul-19N149.1.7.19/PD-032838Commnet_2Question_2.1Score_2.1
149Process_201-Jul-19N149.1.7.19/PD-032838Commnet_2Qestion_2.2Score_2.2
149Process_201-Jul-19N149.1.7.19/PD-032838Commnet_2Qestion_2.3Score_2.3
149Process_201-Jul-19N149.1.7.19/PD-032838Commnet_2Qestion_2.4Score_2.4
150Process_110-Jul-19N150.1.7.19/PD-032477Comment_3Question_3.1Score_3.1
150Process_110-Jul-19N150.1.7.19/PD-032477Comment_3Question_3.2Score_3.2
150Process_110-Jul-19N150.1.7.19/PD-032477Comment_3Question_3.3Score_3.3
150Process_110-Jul-19N150.1.7.19/PD-032477Comment_3Question_3.4Score_3.4

 

Thanks in advance,

 

 

 

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"

 

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

@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

 

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

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

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.

 

v-xuding-msft_0-1602127339059.png

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

WoW! Looks and sounds very promising 🙂

I will try and let you know later on

AlB
Super User
Super User

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 

 

SU18_powerbi_badge

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors