Power Query – Fill Down Column

let
SourceTableName = "Change this to the name of your table"
GroupColumnName = "The header for your group column goes here"
FillColumnName = "The header for the column of values you want to fill"

Source = Excel.CurrentWorkbook(){[Name=SourceTableName]}[Content],
ColNames = Table.ColumnNames(Source),
SortByEmp = Table.Sort(Source, {{GroupColumnName, Order.Ascending}}),
GroupCols = Table.Group(SortByEmp, {GroupColumnName}, {{"GroupCol", each Table.FillDown(Table.Sort(_, {{FillColumnName ,Order.Descending}}),{FillColumnName }), type table}}),
RemCol = Table.RemoveColumns(GroupCols, GroupColumnName),
ExpTable = Table.ExpandTableColumn(RemCol, "GroupCol", ColNames)

in
ExpTable

Advertisements

Power Query – Unique Word List and Count from TXT File

let
numberlists = {{0..31},{33..47},{58..64},{91..96},{123..191}},
combinedlist = List.Combine(numberlists),
punctuationlist = List.Transform(combinedlist, each Character.FromNumber(_)),
Source = File.Contents(“C:\Temp\dickens-david-626.txt”),
CSVSplit = List.Combine(List.Transform(Table.ToList(Csv.Document(Source,1,”#(#)(lf)”,null,1252)), each Text.Split(_, ” “))),
RemovePunctuation = List.Transform(CSVSplit, each Text.Remove(_, punctuationlist)),
TextLower = List.Transform(RemovePunctuation, each Text.Lower(_)),
CreateTable = Table.FromList(TextLower),
FilterBlanks = Table.SelectRows(CreateTable, each ([Column1] “”)),
CountOccurences = Table.Group(FilterBlanks, {“Column1”}, {{“Count”, each Table.RowCount(_), type number}}),
RenameColumn = Table.RenameColumns(CountOccurences, {{“Column1”, “Words”}}),
SortDescending = Table.Sort(RenameColumn,{{“Count”, Order.Descending}})
in
SortDescending