Data Transformations

[milack]

Hello,

First post here... I have two spreadsheets with data laid out in different ways. I need to transform the layout of the "2024 - General - KS House (Johnson)" spreadsheet into the layout of the "2024 - General - KS House (Others)" spreadsheet.

The "Race" and "Candidates" columns in the "2024 - General - KS House (Johnson)" spreadsheet need to get Transposed into their respective columns and linked to the "Precinct Name" column. I hope this makes sense and thanks in advance if you can help!

Milack

2024 - General - KS House (Others)

2024 - General - KS House (Johnson)

  

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.

[Natarajan_M · Accepted solution]

Hi @milack , I was able to recreate your scenario . Ideally you need to use the Unpivot to get the data fromat you want (Column -> rows )

Sample data : 
each column holds the values  :

Transformed data : 
each row holds the values : 

let
    
    Source = Excel.Workbook(
        File.Contents("abcd.xlsx"),
        null, true
    ),

    
    JohnsonSheet = Source{[Item="Source (Johnson - Wide)", Kind="Sheet"]}[Data],

    
    PromotedHeaders = Table.PromoteHeaders(JohnsonSheet, [PromoteAllScalars=true]),

    
    TypedCols = Table.TransformColumnTypes(
        PromotedHeaders,
        {{"County", type text}, {"Precinct", type text}}
    ),

    // Identify ID columns vs value columns
    IdColumns    = {"County", "Precinct"},
    ValueColumns = List.Difference(Table.ColumnNames(TypedCols), IdColumns),

    
    Unpivoted = Table.UnpivotOtherColumns(TypedCols, IdColumns, "Race_Candidate", "Votes"),

    
    FilteredZeros = Table.SelectRows(
        Unpivoted,
        each [Votes] <> null and [Votes] <> 0
    ),

    
    SplitCol = Table.SplitColumn(
        FilteredZeros,
        "Race_Candidate",
        Splitter.SplitTextByEachDelimiter({" - "}, QuoteStyle.Csv, false),
        {"Race", "Candidate"}
    ),

    
    Reordered = Table.ReorderColumns(
        SplitCol,
        {"County", "Precinct", "Race", "Candidate", "Votes"}
    ),

    
    Result = Table.TransformColumnTypes(
        Reordered,
        {{"Votes", Int64.Type}, {"Race", type text}, {"Candidate", type text}}
    )

in
    Result

unpivot.pbix
Thanks 
If this response was helpful in any way, I’d gladly accept a kudo.
Please mark it as the correct solution. It helps other community members find their way faster

[milack]

Huzzah!!! It worked. Thank you very much @Natarajan_M!