Data Transformations

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