Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Multiple fields in a matrix header

Hi,

 

I'm looking to "stack" data fields into the header. Any ideas how to make this happen? Thank you!

 

This is how it looks:

Staff MemberRequisitionRole TitleCompanyOffer Letter SentOrientationForms SignedBadge IssuedStart Date
John DoeXYZ12345Power BI SpecialistGoogle1/1/20231/15/20231/15/20231/17/20231/25/2023
Jane DoesXYZ12346Power BI SpecialistGoogle1/1/20231/15/20231/15/20231/17/20231/25/2023
John SmithXYY12439DeveloperGoogle1/1/20231/15/20231/15/2023 1/25/2023
Jack SmithLKS92839DeveloperAmazon1/1/20231/15/20231/15/20231/17/20231/25/2023
Jane SmithKJSAJ929ConsultantAmazon1/1/20231/15/20231/15/20231/17/20231/25/2023

 

This is how I would like it to look:

 

 John DoeJane DoesJohn SmithJack SmithJane Smith
 XYZ12345XYZ12346XYY12439LKS92839KJSAJ929
 Power BI SpecialistPower BI SpecialistDeveloperDeveloperConsultant
 GoogleGoogleGoogleAmazonAmazon
Offer Letter Sent1/1/20231/1/20231/1/20231/1/20231/1/2023
Orientation1/15/20231/15/20231/15/20231/15/20231/15/2023
Forms Signed1/15/20231/15/20231/15/20231/15/20231/15/2023
Badge Issued1/17/20231/17/2023 1/17/20231/17/2023
Start Date1/25/20231/25/20231/25/20231/25/20231/25/2023
Need Help

1 Reply

  • pmreis's avatar
    pmreis
    Most Valuable Professional
    2 years ago

    Hi Anonymous 


    • You can make the transformation needed in the following order:

      Unpivot other columns except first 4 columns
      Merge first 4 columns
      Pivot Value and don't aggregate

    let
    Source = Data,
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Staff Member", "Requisition", "Role Title", "Company"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Columns",{"Staff Member", "Requisition", "Role Title", "Company"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value")
in
    #"Pivoted Column"

    PFA a file with a solution in a PBIX HERE.

    Please consider marking it as solved for other users to find it.