CODEWITHSUNDEEP
powerquerypowerbi-desktopm
RussGove
RussGove

Reputation: 342

Merge two columns in a table into a new column of type record so I can Pivot on it

I have a table with columns called Id,S1Q1, S1Q1Comments, S2Q2, S2Q2 Comments and so on up to S5Q5 . I am trying to merge the S1Q1 and S1Q1 into a new column called S1Q1Record which has A record with 3 fields --Score (with the value of S1Q1), Comments (which has the value of S1Q1Comments and ReportKey(which would be 'S1Q1'). Same for S1Q2 thru S5Q5.

(S1Q1 means Section1 Question 1)

I figure this way I can the Unpivot those mew columns and end up with a table that just has the columns ID, ReportKey, Score, and Comments.

I've tried this this just to see if I can create a column containing a record :

let
    Source = Base5s,
    step1 = Table.FirstN(Source,3),
    step2=Table.AddColumn(step1, "S1Q1Record", each 
       Record.SelectFields(_ , S1Q1))
in
    step2

but column S1Q1Record just shows an error saying enter image description here

I need to get the value of S1Q1, S1Q1Comments and the literal string "S1Q1" into a record with column names Score, Comments and ReportKey Any ideas how to do this?

Upvotes: 0

Views: 75

Answers (1)

Patrick FitzGerald
Patrick FitzGerald

Reputation: 3630

No need for the new S1Q1Record column. You can create the table you want in just a few steps using the buttons available in the ribbon, no custom M code is needed. Here is a complete example, starting with the following sample table:

table_start


Here is the M code to create the sample table and reshape it as wanted. I have renamed the steps for clarity, that and the function names should be enough to understand which buttons were used to generate each step:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIHYuf83NzUvBIFRyDbDIkfoRSrE61kBBQxRhJ1ArItkPiRYFUgFZZIos5AtgkSP0opNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, S1Q1 = _t, S1Q1Comments = _t, S1Q2 = _t, S1Q2Comments = _t]),
    #"Unpivoted All Columns Except ID" = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"),
    #"Split Attribute Column After 4th Character" = Table.SplitColumn(#"Unpivoted All Columns Except ID", "Attribute", Splitter.SplitTextByPositions({0, 4}, false), {"Attribute.1", "Attribute.2"}),
    #"Filled Empty Cells With ""Score""" = Table.ReplaceValue(#"Split Attribute Column After 4th Character","","Score",Replacer.ReplaceValue,{"Attribute.2"}),
    #"Pivoted Attribute.2 Column" = Table.Pivot(#"Filled Empty Cells With ""Score""", List.Distinct(#"Filled Empty Cells With ""Score"""[Attribute.2]), "Attribute.2", "Value"),
    #"Renamed ReportKey Column" = Table.RenameColumns(#"Pivoted Attribute.2 Column",{{"Attribute.1", "ReportKey"}}),
    #"Locked In All Data Types" = Table.TransformColumnTypes(#"Renamed ReportKey Column",{{"ID", Int64.Type}, {"ReportKey", type text}, {"Score", Int64.Type}, {"Comments", type text}})
in
    #"Locked In All Data Types"

And here is the result:

table_end

Upvotes: 1

Related Questions