Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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,
Solved! Go to 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 :))
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 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):
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,
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! |
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.
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
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
User | Count |
---|---|
24 | |
13 | |
11 | |
10 | |
7 |
User | Count |
---|---|
44 | |
26 | |
21 | |
16 | |
12 |