Forum Discussion

renee_crozier's avatar
renee_crozier
Brass Contributor
Nov 27, 2024

Reorder Cell Data Using Power Query

I'm trying to take a list of items, reorder the data in a single cell, and then alphabetize the column. Alphabetizing the column is easy but reordering the data in a cell is tripping me up. I know I ...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 27, 2024

With Power Query that could be

let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    PromoteHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    TextsToLists = Table.AddColumn(
        PromoteHeaders,
        "Lists",
        each List.Sort( Text.Split( [Original List], ", " ) ) ),

    names = List.Transform(
        {1..List.Max( List.Transform( TextsToLists[Lists], (q) => List.Count(q) ) ) },
        (s) => "Col." & Number.ToText(s, "000") ),
    namesSorted = {names{0}} & List.Sort( List.Skip( names ), Order.Descending ),
    order = List.Transform( namesSorted, (q) => Order.Ascending ),
    sortOrder = List.Zip( {namesSorted, order } ),

    ExtractListValues = Table.TransformColumns(
        TextsToLists,
        {"Lists", each Text.Combine(List.Transform(_, Text.From), "="),
        type text}),
    SplitValues = Table.SplitColumn(
        ExtractListValues,
        "Lists",
        Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv),
        names),
    SortOutcome = Table.Sort(
        SplitValues,
        sortOrder),
    CombineOutcome = Table.AddColumn(
        SortOutcome,
        "Outcome",
        each Text.Combine( Record.FieldValues( Record.SelectFields(_, names) ), ", " ) ),
    SelectColumns = Table.SelectColumns(CombineOutcome,{"Outcome"})
in
    SelectColumns

which gives

 

  • renee_crozier's avatar
    renee_crozier
    Brass Contributor
    Dec 06, 2024

    When I use your code, looking at the dummy data, and then modifying it to match my spreadsheet, I just get Null in each cell. Did I miss something?

    The modified code:

    let
    Source = Excel.Workbook(File.Contents("\Source Documents\AllContentReport.xlsx"), null, true),
    AllContentReport_Sheet = Source{[Item="AllContentReport",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(AllContentReport_Sheet, [PromoteAllScalars=true]),
    #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"ContentOwner"}),
    TextsToLists = Table.AddColumn(
        #"Removed Other Columns",
        "Lists",
        each List.Sort( Text.Split( [ContentOwner], "," ) ) ),

    names = List.Transform(
        {1..List.Max( List.Transform( TextsToLists[Lists], (q) => List.Count(q) ) ) },
        (s) => "Col." & Number.ToText(s, "000") ),
    namesSorted = {names{0}} & List.Sort( List.Skip( names ), Order.Descending ),
    order = List.Transform( namesSorted, (q) => Order.Ascending ),
    sortOrder = List.Zip( {namesSorted, order } ),

    ExtractListValues = Table.TransformColumns(
        TextsToLists,
        {"Lists", each Text.Combine(List.Transform(_, Text.From), "="),
        type text}),
    SplitValues = Table.SplitColumn(
        ExtractListValues,
        "Lists",
        Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv),
        names),
    SortOutcome = Table.Sort(
        SplitValues,
        sortOrder),
    CombineOutcome = Table.AddColumn(
        SortOutcome,
        "Outcome",
        each Text.Combine( Record.FieldValues( Record.SelectFields(_, names) ), "; " ) ),
    SelectColumns = Table.SelectColumns(CombineOutcome,{"Outcome"})
in
    SelectColumns

     

Resources