bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddToList = Table.RemoveColumns( Table.DuplicateColumn( Table.AddColumn(Source, "StripList", each Text.ToList( [RSTRIP Chars])), "Input String", "OriginalString"), "RSTRIP Chars"),
    AddOrigToList = Table.AddColumn(AddToList, "OrigToList", each Text.ToList( Text.Reverse( [OriginalString] ))),
    Expand = Table.ExpandListColumn(AddOrigToList, "OrigToList"),
    AddInStriplist = Table.AddColumn(Expand, "InStripList", each if List.Contains( [StripList], [OrigToList]) then "Strip" else "Keep"),
    Remove = Table.SelectColumns(AddInStriplist,{"InStripList", "Input String"}),
    Reorder = Table.ReorderColumns(Remove,{"Input String", "InStripList"}),
    GroupKindLocal = Table.Group(Reorder, {"Input String", "InStripList"}, {{"Remove", each Table.RowCount(_), Int64.Type}}, GroupKind.Local),
    Regroup = Table.Group(GroupKindLocal, {"Input String"}, {{"All", each _, type table [Input String=text, InStripList=text, Remove=number]}}),

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddQNumber = Table.FillUp( Table.AddColumn(Source, "QNum", each if [Seq] = "#" then null else [Seq]), {"QNum"}),
    AddQHeader = Table.FillDown( Table.AddColumn(AddQNumber, "QHeader", each if [Seq] = "#" then Text.From( [QNum] ) & ". " & [Question] else null), {"QHeader"}),
    AddIndex = Table.AddIndexColumn(AddQHeader, "Index", 1, 1, Int64.Type),
    Filter = Table.RemoveColumns( Table.SelectRows(AddIndex, each ([Seq] <> "#")), {"Seq", "QNum"}),
    Group = Table.Group(Filter, {"QHeader"}, {{"All", each _, type table [Question=text, Correct=nullable text, QHeader=text, Index=number]}}),
    AddOptionNum = Table.RemoveColumns( Table.AddColumn(Group, "Option", each Table.AddIndexColumn( [All], "Option", 1, 1 )), "All"),
    Expand = Table.ExpandTableColumn(AddOptionNum, "Option", {"Question", "Correct", "Index", "Option"}, {"Question", "Correct", "Index", "Option"}),
    AddOptions = Table.RemoveColumns( Table.AddColumn(Expand, "Options"

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Dupe = Table.DuplicateColumn(Source, "Numbers", "Numbers2"),
    Split = Table.ExpandListColumn(Table.TransformColumns(Dupe, {{"Numbers2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Numbers2"),
    Trim = Table.TransformColumns(Split,{{"Numbers2", Text.Trim, type text}}),
    Group = Table.Group(Trim, {"Numbers"}, {{"All", each _, type table [Numbers=text, Numbers2=text]}}),
    AddIndex = Table.RemoveColumns( Table.AddColumn(Group, "Index", each Table.AddIndexColumn( [All], "Index", 1, 1 )), "All"),
    Expand = Table.ExpandTableColumn(AddIndex, "Index", {"Numbers2", "Index"}, {"Numbers2", "Index"}),
    AddIndexAll = Table.AddIndexColumn(Expand, "IndexAll", 1, 1, Int64.Type),
    Split2 = Table.ExpandListColumn(Table.TransformColumns(AddIndexAll, {{"Numbers2", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Retype = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    AddDayName = Table.AddColumn(Retype, "DayName", each Date.DayOfWeekName( [Date] )),
    Filter = Table.RemoveColumns( Table.SelectRows(AddDayName, each ([DayName] <> "Sunday" and [DayName] <> "Saturday")), "DayName"),
    Group = Table.Group(Filter, {"Emp ID"}, {{"All", each _, type table [Emp ID=number, Date=nullable date]}}),
    AddIndex0 = Table.AddColumn(Group, "Index0", each Table.AddIndexColumn( [All], "Index0", 0, 1)),
    AddIndex1 = Table.RemoveColumns( Table.AddColumn(AddIndex0, "Index1", each Table.AddIndexColumn([Index0], "Index1", 1, 1)), {"All", "Index0"}),
    Expand = Table.ExpandTableColumn(AddIndex1, "Index1", {"Date", "Index0", "Index1"}, {"Date", "Index0", "Index1.1"}),
    Expand2 = Table.TransformColumnNames(Expand, Text.Trim, [MaxLength = 3]),

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TextTable = Table.RenameColumns( Table.SelectColumns(Source, "Column9"), {"Column9", "Text"}),
    #"Filtered Rows" = Table.SelectRows(TextTable, each ([Text] <> null)),
    #"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "Text", "Text2"),
    #"Split Column by Position" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated Column", {{"Text2", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Text2"),
    Final1 = Table.Buffer( Table.TransformColumns(#"Split Column by Position",{{"Text2", Text.Upper, type text}})),
    #"Added Index" = Table.AddIndexColumn(Final1, "Index", 1, 1, Int64.Type),
    TempTable = Table.RemoveFirstN( Table.RemoveColumns( Source, {"Column8", "Column9"}), 1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(TempTable, {"Column1"}, "Attribute", "Value"),

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ReType = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type any}}),
    RemoveBlankRows = Table.SelectRows(ReType, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    ReplaceNull = Table.ReplaceValue(RemoveBlankRows,null,"Date",Replacer.ReplaceValue,{"Column2"}),
    UnpivotOther = Table.UnpivotOtherColumns(ReplaceNull, {"Column1"}, "Attribute", "Value"),
    AddNumbers = Table.AddColumn(UnpivotOther, "Numbers", each if List.ContainsAny( Text.ToList( Text.From( [Value] )), {"0".."9"}) then [Value] else null),
    AddLabels = Table.FillDown( Table.AddColumn(AddNumbers, "Labels", each if List.Contains( {"Samples", "Date", "Prodict", "Plan", "Actual", "Duration"}, [Value]) then [Value] else null), {"Labels"}),
    AddSite = Table.FillDown( Table.AddColumn(AddLabels, "Site", each try if Text.StartsWith( [Column1], "Plant" ) then [Colum

bjulius

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sUorViVbyyS9LBTMcS4vyixLBTNeczIpMiLRrSWpRXmZJJURtYnp+fh6YGZSYmZeUXw5mB2fmZOSXppaUAA2KBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Words = _t]),
    NumList = {1..101},
    ToTable = Table.FromList(NumList, Splitter.SplitByNothing(), {"Number"}, null, ExtraValues.Error),
    #"Run R script" = R.Execute("# 'dataset' holds the input data for this script#(lf)#(lf)library(primes)#(lf)df <- dataset#(lf)df$isprime <- is_prime(df$Number)#(lf)df",[dataset=ToTable]),
    #"""df""" = #"Run R script"{[Name="df"]}[Value],
    Filter = Table.SelectColumns(Table.SelectRows(#"""df""", each ([isprime] = true)), "Number"),
    Lookup = Table.FromColumns( {Filter[Number], {"A".."Z"}},{"Value","Letter"}),
    WordTable = Table.DuplicateColumn(Source, "Words", "Word" ),
    Upper = Table.TransformColumns(WordTable,{{"Word", Text.Upper, type text}}),

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    CompSales =  Table.Group(Source, {"Company"}, {{"Sales", each List.Sum([Sales]), type number}}),
    DateType = Table.TransformColumnTypes(Source,{{"From", type date}, {"To", type date}}),
    AddIndex = Table.AddIndexColumn(DateType, "Index", 1, 1, Int64.Type),
    AddDateList = Table.AddColumn(AddIndex, "DateList", each List.Dates( [From], Number.From([To]) - Number.From([From]) + 1, #duration(1, 0, 0, 0))),
    Expand = Table.ExpandListColumn(AddDateList, "DateList"),
    AddMonthNum = Table.AddColumn(Expand, "MonthNum", each Date.Month( [DateList] )),
    AddShortMonth = Table.AddColumn(AddMonthNum, "ShortMonth", each Text.Start( Date.MonthName([DateList]), 3)),
    RemoveWeekends = Table.SelectRows( Table.AddColumn(AddShortMonth, "AddDay", each Date.DayOfWeekName([DateList])), each not Text.StartsWith( [AddDay], "S")),

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddToListNoNulls = Table.ExpandListColumn( Table.AddColumn(Source, "Letters", each List.RemoveMatchingItems( Text.ToList( [String]), {" "}  )), "Letters"),
    Group = Table.Group(AddToListNoNulls, {"String"}, {{"All", each _, type table [String=text, n=number, TexttoLIstNoNulls=text]}}),
    AddIdx = Table.RemoveColumns( Table.AddColumn(Group, "Index", each Table.AddIndexColumn( [All], "Idx", 0, 1)), "All"),
    Expand = Table.ExpandTableColumn(AddIdx, "Index", {"n", "Letters", "Idx"}, {"n", "Letters", "Idx"}),
    AddMod = Table.AddColumn(Expand, "Mod", each Number.Mod( [Idx], [n] )),
    AddIntDiv =  Table.AddColumn(AddMod, "IntDiv", each Number.IntegerDivide( [Idx], [n] )),
    #"Removed Columns" = Table.RemoveColumns(AddIntDiv,{"n", "Idx"}),
    Regroup = Table.Group(#"Removed Columns", {"String"}, {{"All", each _, type table [String=text, Letters=text, Idx=number, Mod=number]}}),

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Dupe1'" = Table.DuplicateColumn(Source, "Number1", "Number1Digits"),
    ReType1 = Table.TransformColumnTypes(#"Dupe1'",{{"Number1Digits", type text}}),
    ToList1 = Table.AddColumn(ReType1, "Digits1", each Text.ToList( [Number1Digits] )),
    Expand1 = Table.ExpandListColumn(ToList1, "Digits1"),
    Group1 = Table.Group(Expand1, {"Number1", "Number2", "Digits1"}, {{"Count1", each Table.RowCount(_), Int64.Type}}),

     Dupe2 = Table.DuplicateColumn(Source, "Number2", "Number2Digits"),
    ReType2 = Table.TransformColumnTypes(Dupe2,{{"Number2Digits", type text}}),