Forum Discussion
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 g...
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 :))
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"
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