bjulius

let
    Source = Table.AddIndexColumn( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "Index", 1, 1),
    AddNumLIst = Table.AddColumn(Source, "NumList", each List.Select( List.Numbers( [From], [To] - [From] +1 ), Number.IsOdd)),
    Expand = Table.ExpandListColumn(AddNumLIst, "NumList"),
    AddIndex2 = Table.AddIndexColumn(Expand, "Index2", 1, 1, Int64.Type),
    SplltToRows = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(AddIndex2, {{"NumList", type text}}, "en-US"), {{"NumList", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "NumList"),
    Group = Table.RemoveColumns( Table.Group(SplltToRows, {"Index2"}, {{"All", each _, type table [From=number, To=number, Index=number, NumList=nullable text, Index2=number]}}), "Index2"),
    UniqueVsTotal = Table.AddColumn(Group, "Condition", each [
    a = List.Count( [All][NumList] ),
    b = List.Count( List.Distinct( [All][NumList] )),

bjulius

let
    Source = Table.RemoveColumns( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "Column1"),
    Crossjoin = Table.SelectRows( Table.RemoveColumns( Table.ExpandListColumn(Table.AddColumn(Source, "Crossjoin", each Source[Numbers]), "Crossjoin"), "Sum"), each [Numbers] <> [Crossjoin] ),
    CrossjoinSum = Table.SelectRows( Table.ExpandListColumn( Table.AddColumn(Crossjoin, "CrossjoinSum", each Source[Sum]), "CrossjoinSum"), each [CrossjoinSum] <> null),
    FIlterSums = Table.RemoveColumns( Table.SelectRows( Table.AddColumn(CrossjoinSum, "Custom", each if [Numbers] + [Crossjoin] = [CrossjoinSum] then true else false), each [Custom] = true), "Custom"),
    Concat = Table.AddColumn(FIlterSums, "Concat", each [
        a = List.Min( {[Numbers], [Crossjoin]} ),
        b = List.Max( {[Numbers], [Crossjoin]} ),
        c = Text.From(a) & "+" & Text.From(b)
        ][c]),

bjulius

let
    Source = Table.DuplicateColumn( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "Month-Year", "Mo-Yr"),
    Split = Table.SplitColumn(Source, "Mo-Yr", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Month", "Year"}),
    ReType = Table.TransformColumnTypes(Split,{{"Month", Int64.Type}, {"Year", Int64.Type}}),
    AddFY = Table.AddColumn(ReType, "FY", each if [Month] < 4 then [Year] else [Year] + 1),
    GroupSort = Table.Group(AddFY, {"Name", "FY"}, List.Sort( {{"All", each _, type table [Name=text, #"Month-Year"=text, Sales=number, Month=nullable number, Year=nullable number, FY=number]}}, each {"Month", Order.Ascending})),
    RTTable = Table.AddColumn(GroupSort, "RunTot", each [
        Idx = Table.AddIndexColumn( [All], "Index", 1, 1),
        SalesAmt = Idx[Sales],
        MoYr = Idx[#"Month-Year"],

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Translate = 
    [
        Chars = Table.FromList({"A".."Z", "a".."z"}, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        Vowels = Table.PrefixColumns( Table.FromList({"A", "E", "I", "O", "U", "a", "e", "i", "o", "u"}, Splitter.SplitByNothing(), null, null, ExtraValues.Error), "V"),
        Join = Table.Sort( Table.Join( Chars, "Column1", Vowels, "V.Column1", JoinKind.LeftOuter), {"Column1", Order.Ascending}),
        Fill = Table.RenameColumns( Table.FillDown(Join,{"V.Column1"}), {{"Column1", "Letters"}, {"V.Column1", "Vowels"}})
    ][Fill],
    

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.RenameColumns( Table.SplitColumn(Source, "Names", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Names.1", "Names.2"}), {{"Names.1", 
"FirstName"}, {"Names.2", "Surname"}}),

    Vowels = {"A", "E", "I", "O", "U", "a", "e", "i", "o", "u"},
    Consanants = List.RemoveMatchingItems( {"A".."Z", "a".."z"}, Vowels),
    
    Surname = Table.AddColumn(Split, "SurCode", each [ 
            SurCons =  Text.Upper(Text.Remove( [Surname], Vowels)),

bjulius

let
    Source = Table.AddIndexColumn( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "Index", 1, 1),
    SplitToRows = Table.AddIndexColumn( Table.ExpandListColumn(Table.TransformColumns(Source, {{"Text", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Text"), "SortOrder", 1, 1),
    Target = Table.AddIndexColumn( Table.FromList( {"A".."Z", "a".."z"}, Splitter.SplitByNothing(), {"Letters"}, null, ExtraValues.Error), "Position", 1, 1),
    JoinPos = Table.RemoveColumns( Table.Sort( Table.Join(SplitToRows, "Text", Target, "Letters", JoinKind.LeftOuter), {"SortOrder", Order.Ascending}), "Letters"),
    AddModShift = Table.AddColumn(JoinPos, "ModShift", each 
        if List.Contains( {"0".."9"}, [Text] ) then  Number.Mod( [Shift], 10) else 
        if [Position] = null then null else 
        Number.Mod( [Shift] + [Position], 52)),
    JoinShifted = Table.Sort( Table.Join( AddModShift, "ModShift", Table.PrefixColumns( Target, 

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    ReplaceSeq = Table.RenameColumns( Table.ReplaceValue(Source,"Seq",null,Replacer.ReplaceValue,{"City"}), {"City", "CityTemp"}),
    AddCity = Table.AddColumn(ReplaceSeq, "City", each if [Seq] = null then null else [CityTemp] ),
    ReType = Table.TransformColumnTypes(AddCity,{{"CityTemp", Int64.Type}, {"Column1", type text}}),
    Rename = Table.RenameColumns(ReType,{{"Column1", "DateTemp"}, {"Sub Total", "AmountTemp"}, {"State", "Class"}, {"CityTemp", "No"}}),
    AddState = Table.AddColumn(Rename, "State", each if List.Contains( {"Class", "Local", "Council"}, [Class] ) then null else [Class]),
    FillDown = Table.FillDown(AddState,{"City", "State", "Seq"}),
    Filter = Table.SelectRows(FillDown, each ([DateTemp] <> null)),
    RemoveTop = Table.Skip(Filter,1),

bjulius

let
    Source = Table.AddIndexColumn( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "Index", 0, 1),

    AddConsecutive = Table.AddColumn(Source, "Consecutive", each 
    [
        PriorAlphabets = try Source[Alphabets]{[Index]-1} otherwise null,
        PriorSeq =  try Source[Seq]{[Index]-1} otherwise [Alphabets],
        Consecutive = if [Alphabets] <> PriorAlphabets then "Y" else
            if [Seq] = PriorSeq + 1 then "Y" else 
            "N"

bjulius

(#"Input Number of Years" as number) as table =>
let
    Source = 
    [
    Today = DateTime.FixedLocalNow(),
    CurrYear = Date.Year( Today ),
    EndYear = if Number.From( #date( CurrYear, 12, 25)) > Number.From( Today ) then CurrYear - 1 else CurrYear,
    StartYear = EndYear - #"Input Number of Years" + 1,
    StartDate = #date(StartYear, 12, 25),
    EndDate =  #date(EndYear, 12, 25),

bjulius

(#"Select Table to Create Metadata Summary" as table) =>
  let
    Schema = Table.SelectColumns(
      Table.Schema(#"Select Table to Create Metadata Summary"), 
      {"Name", "Position", "TypeName", "Kind", "IsNullable"}
    ), 
    Profile = Table.Profile(#"Select Table to Create Metadata Summary"), 
    Join = Table.Sort(Table.Join(Schema, "Name", Profile, "Column"), {"Position", Order.Ascending}), 
    ReType = Table.TransformColumnTypes(
      Join,