CODEWITHSUNDEEP
powerbipivot
Joe Crozier
Joe Crozier

Reputation: 1036

Pivoting and unpivoting data in power Bi

I have data that looked like this:

enter image description here

It has a unique recordid column, and then three repeating columns: email, is_this_study_feasible, and please_advise. (is_this_study_feasible_v2...etc..)

I'd love to transform this from wide to long and be left with something like this where I can tell what each person said about each record id:

enter image description here

I'm most of the way there but running into some problems. I've followed many of the steps from this article: https://kohera.be/power-bi/how-to-unpivot-twice/ and my data currently looks like this:

enter image description here

The problem is, when I select the attribute column and "pivot" it using values from the value column (with no aggregation), I get thrown errors:

enter image description here

Looks like the errors are from trying to pivot identical values. I.e. row 17 and 21 are effectively the same.

So I read somewhere I could add an index column and then pivot, KINDA works, but everything gets put in the wrong spot:

enter image description here

I need to be able to tell what Jcrozier (me, from the "email1" column) said about whether the study was feasible (the first "is_this_feasible" column).

Any tips would be appreciated

Update

David's code below seemed to work, but I ran into a problem. When I added data (not new columns/rows, just a filled in field) that wasn't in original question like so:

enter image description here

It didn't make it "past" that code: enter image description here

I've included the .pbix file here for reference: https://1drv.ms/u/s!AnsEucAtFS_SlsxHIs-VCHr-BHUxew?e=e3PlVq

Upvotes: 1

Views: 395

Answers (2)

davidebacci
davidebacci

Reputation: 30174

Go to advanced editor and paste in the text below and it will work for you. I have removed the token value in line 3 so don't forget to update that from your original query.

 let
        actualUrl = "https://redcap.lifespan.org/redcap/api/",
        record  =[token="TOKEN_REMOVED",
        content="record",
        format="csv"
        ],
        body = Text.ToBinary(Uri.BuildQueryString(record)),
        options = [Headers =[#"Content-type"="application/x-www-form-urlencoded"], Content=body],
        result = Web.Contents(actualUrl, options),
        #"Imported CSV" = Csv.Document(result,[Delimiter=",", Columns=33, Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
        #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
        #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"protocol_title", "short_title", "principal_investigator", "filepath_for_protocol", "is_imaging_manual_included", "filepath_for_imaging_manua", "number_of_patients_expecte", "approximate_length_of_the", "approximate_number_of_part", "name_of_research_coordinat", "email_address_of_research", "any_other_special_instruct", "protocol_information_complete", "emailsurvey", "email_list_complete", "collaboration_survey_complete", "collaboration_survey_2_complete", "collaboration_survey_3_complete", "collaboration_survey_4_complete", "method_of_image"}),
        Custom1 = let
        #"Added Custom" = Table.AddColumn(#"Removed Columns", "ListOfRecords", each {[email = [email1], is_this_study_feasible =[is_this_study_feasible], please_advise = [please_advise] ],[email = [email2], is_this_study_feasible =[is_this_study_feasible_v2] , please_advise = [please_advise_v2]],[email = [email3], is_this_study_feasible =[is_this_study_feasible_v3], please_advise = [please_advise_v3] ],[email = [email4], is_this_study_feasible =[is_this_study_feasible_v4], please_advise = [please_advise_v4] ]}),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"recordid", "ListOfRecords"}),
        #"Expanded ListOfRecords" = Table.ExpandListColumn(#"Removed Other Columns", "ListOfRecords"),
        #"Expanded ListOfRecords1" = Table.ExpandRecordColumn(#"Expanded ListOfRecords", "ListOfRecords", {"email", "is_this_study_feasible", "please_advise"}, {"email", "is_this_study_feasible", "please_advise"})
    in
        #"Expanded ListOfRecords1",
        #"Renamed Columns" = Table.RenameColumns(Custom1,{{"email", "Respondent"}, {"is_this_study_feasible", "Is This Study Feasible?"}, {"please_advise", "Please Advise"}}),
        #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"recordid"}, #"Protocol Info", {"recordid"}, "Protocol Info", JoinKind.LeftOuter),
        #"Expanded Protocol Info" = Table.ExpandTableColumn(#"Merged Queries", "Protocol Info", {"Protocol Title"}, {"Protocol Info.Protocol Title"})
    in
        #"Expanded Protocol Info"

Upvotes: 1

davidebacci
davidebacci

Reputation: 30174

Here you go.

enter image description here

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XU3NCoMwDH6V0rOIDrz3umcQD7FkJpoaaetlT7+O7qCDwPeXj28cbW8bu/qob8bohF+YDthbjUvx43aSBNgd/wWa8KBfaegHR5oDsLReQ0nL9RW6Cl5VKhMI+GVTM9pHIbPO7tJcFa9yQ84EtwcWcfep7rb4NAQZDWeT1ITTU7XTGbExorolAxJ5oWyn6QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [recordid = _t, email1 = _t, email2 = _t, email3 = _t, email4 = _t, is_this_study_feasible1 = _t, is_this_study_feasible2 = _t, is_this_study_feasible3 = _t, is_this_study_feasible4 = _t, please_advise1 = _t, please_advise2 = _t, please_advise3 = _t, please_advise4 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "ListOfRecords", each {[email = [email1], is_this_study_feasible =[is_this_study_feasible1], please_advise = [please_advise1] ],[email = [email2], is_this_study_feasible =[is_this_study_feasible2] , please_advise = [please_advise2]],[email = [email3], is_this_study_feasible =[is_this_study_feasible3], please_advise = [please_advise3] ],[email = [email4], is_this_study_feasible =[is_this_study_feasible4], please_advise = [please_advise4] ]}),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"recordid", "ListOfRecords"}),
    #"Expanded ListOfRecords" = Table.ExpandListColumn(#"Removed Other Columns", "ListOfRecords"),
    #"Expanded ListOfRecords1" = Table.ExpandRecordColumn(#"Expanded ListOfRecords", "ListOfRecords", {"email", "is_this_study_feasible", "please_advise"}, {"email", "is_this_study_feasible", "please_advise"})
in
    #"Expanded ListOfRecords1"

Upvotes: 0

Related Questions