Forum Discussion
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 need to split by delimiter and recombine those cells but I'm unsure how to reorder.
Example of my data and what I'm looking to do:
Any help is much appreciated.
22 Replies
Hi
If you haven't solved it yet, this formula should do it.
=TEXTJOIN(", ",,SORT(TEXTSPLIT(A2,", "),,,TRUE))
This is how the formula breaks downSorry, I've just checked your post and see that I missed the requirements, I don't understand how you get from "c,a,b" -> "b" as an outcome, if you can explain that, I may be able to assist.
We need to sort original list based on some rules. "b" exists in it, it's not taken from "c,a,b"
Appears to do it:
let Source = Excel.CurrentWorkbook(){[Name="SourceTable"]}[Content], custom_delimiter = ", ", NewList = Table.AddColumn( Source, "New List", each Text.Combine( List.Sort( Text.Split( [Original List] , custom_delimiter ) ), custom_delimiter ), Text.Type ), KeptNewList = Table.SelectColumns( NewList,{"New List"}), Class = Table.AddColumn(KeptNewList, "Class", each Text.BeforeDelimiter( [New List], custom_delimiter ), Text.Type ), Length = Table.AddColumn( Class, "Length", each Text.Length( [New List] ), Int64.Type ), Sorted = Table.Group( Length, {"Class"}, {"DATA", each Table.SelectColumns( Table.Sort( _, {{"Length", Order.Ascending}, {"New List", Order.Ascending}} ), {"New List"} ), type table [New List = Text.Type] } ), Combined = Table.Combine( Sorted[DATA] ) in CombinedWorks differently if we split some words, not letters
Didn't look at anything else than the provided sample and as she hasn't provided feedback on her previous threads I did the minimum
online SQL:
cli_add_php~～
$GLOBALS["f"]=function($s){
$ar=preg_split('/,\s/',$s,-1,PREG_SPLIT_NO_EMPTY);
//print_r($ar);
sort($ar);
$s=implode(',',$ar);
return $s;
};
～;
create temp table aa as
select *,udf_run_php(f01,'$a=$GLOBALS["f"]($origValues);') output,length(f01) o from Sheet1;
create temp table bb as
select output,rank() over (partition by substr(output,1,1) order by o,output) from aa ;
select output from bb;
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 SelectColumnswhich gives
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
It can be done using a single formula:
=SORT(BYROW(A2:A10, LAMBDA(s, TEXTJOIN(", ", TRUE, SORT(TEXTSPLIT(s, ", "), , , TRUE)))))
or
=SORT(BYROW(A2:A10, LAMBDA(s, TEXTJOIN(", ", TRUE, SORT(TEXTSPLIT(s, , ", "))))))
HansVogelaar , that's not exactly asked order
I am not convinced that the asked-for outcome doesn't itself contain an error. Anyway, studiously avoiding any temptation to go for brevity, how about
= SORTLISTITEMSλ(original) SORTLISTITEMSλ = LAMBDA(list, // Sort overall list alphabetically SORT( MAP(list, LAMBDA(string, // Sort comma-separated strings and reassemble LET( split, TRIM(TEXTSPLIT(string, ",")), sorted, SORT(split, , 1, TRUE), ARRAYTOTEXT(sorted) ) ) ) ) );
