bjulius

let
  Source = #"Sentences Raw",
  AddedIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
  SplitBySpaces = Table.ExpandListColumn(Table.TransformColumns(AddedIndex, {{"Sentence", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Sentence"),
  TrimmedText = Table.TransformColumns(SplitBySpaces,{{"Sentence", Text.Trim, type text}}),
  WordLength = Table.AddColumn(TrimmedText, "WordLength", each Text.Length( [Sentence] )),
  MaxLengthBySentence = Table.Group(WordLength, {"Index"}, {{"AllData", each _, type table [Sentence=text, Index=number, WordLength=number]}, {"MaxLength", each List.Max([WordLength]), type number}}),
  ExpandedAllData = Table.ExpandTableColumn(MaxLengthBySentence, "AllData", {"Sentence", "WordLength"}, {"Sentence", "WordLength"}),
  AddedLengthEqMaxLen = Table.AddColumn(ExpandedAllData, "LengthEqMaxLen", each if [WordLength] = [MaxLength] then 1 else 0),
  FilteredToMaxLenWords = Table.

bjulius

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSk4sUShPTSwqVshILFGK1YlWKslIVUgCiigU5CRWFiuUZ5ZkKCQX5SeDJT1SFQqKKhOLFUryFUAK0/NT9JRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OriginalText = _t]),
  AddSpace = Table.AddIndexColumn( Table.ReplaceValue(Source,"."," .",Replacer.ReplaceText,{"OriginalText"}), "SentenceIndex", 1, 1),
  SplitBySpace = Table.ExpandListColumn(Table.TransformColumns(AddSpace, {{"OriginalText", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "OriginalText"),
  MergedReplaceText = Table.ExpandTableColumn( Table.NestedJoin(SplitBySpace, {"OriginalText"}, ReplaceText, {"From"}, "ReplaceText", JoinKind.LeftOuter), "ReplaceText", {"To"}),
  FinText = Table.AddColumn( MergedReplaceText, "FinalText", each if [To] = null then [OriginalText] else [To]),
  GroupBySent = Table.Group(Fi

bjulius

let
    Source = #"Marks Raw",
    Headers = List.Select( Table.ColumnNames(Source), each not Text.Contains( _, "Subject") ),
    
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, Headers, "Attribute", "Value"),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"ID", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns1", each ([Value] <> "") and ([Value.1] <> "")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Attribute", Splitter.SplitTextByDelimiter("Subject", QuoteStyle.Csv), {"Attribute.1.1", "Attribute.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Attribute.1.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute.2", "Subject"}}),

bjulius

let
   
    Source = Table.RenameColumns( Table.ReorderColumns( Table.AddIndexColumn( Table.FromList({"A".."Z"}, Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Index", 1, 1 ), {"Index", "Column1"}), {"Column1", "Letters"}),
    CustIdx = Table.AddIndexColumn( Table.FromList({"A".."Z"}, Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Index", 1, 1 ),
    AddConcat = Table.SelectColumns( Table.AddColumn(CustIdx, "Concat", each "Column" & Text.From( [Index] )), "Concat"),
    Crossjoin = Table.ExpandTableColumn( Table.AddColumn(Source, "Custom", each AddConcat), "Custom", {"Concat"}, {"Concat"}),
    Split = Table.SplitColumn(Table.DuplicateColumn(Crossjoin, "Concat", "Concat - Copy"), "Concat - Copy", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Concat - Copy.1", "Concat - Copy.2"}),
    NumFrom = Table.TransformColumnTypes(Split,{{"Concat - Copy.2", Int64.Type}}),
    TestCol = Table.SelectColumns( Table.AddColumn(NumFrom, "Test", each 

bjulius

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LY1LDsIwDETvkjWXoMCKIiHaXdSFFVnYwiGSnarq7SlOdm9G84kxzFQyWFhOMbxWMxRxvmWW3ekOil+niVgFmzvCdtgJXVxI2SpDiz2hKicGFwNL35l3Qe1xOHba58jWgg989/6UuZLTtWip1OrDmj4OZ+H/7fID", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Members = _t]),
    Random = Table.AddColumn(Source, "Random", each Number.Random(), type number),
    Sort = Table.Sort(Random,{{"Random", Order.Ascending}}),
    Index = Table.AddIndexColumn(Sort, "Index", 1, 1, Int64.Type),
    Modulo = Table.RemoveColumns( Table.AddColumn(Index, "Modulo", each Number.Mod( [Index], 6 )), "Random"),
    Pivoted = Table.FromColumns( List.Transform( Table.ToColumns( Table.RemoveColumns( Table.Pivot(Table.TransformColumnTypes(Modulo, {{"Modulo", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Modulo, {{"Modulo", type text}}, "en-US")[Modulo]), "Modulo", "Members"), "Index")), each List.RemoveNulls( _

bjulius

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcqxEcAwCATBXogdGHj0UAuj/tvwCPmiDa5bVpbsp0VBrqvFrFfHAHIQJP/RHIGh12loiLqnejBN9v4A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Numbers = _t]),
    Index = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    ToList = Table.AddColumn(Index, "NumList", each Text.ToList( [Numbers] )),
    Expand = Table.ExpandListColumn(ToList, "NumList"),
    Int64 = Table.TransformColumnTypes(Expand,{{"NumList", Int64.Type}}),
    Sequential0 = Table.AddIndexColumn(Int64, "Sequence0", 0, 1, Int64.Type),
    Sequential1 = Table.AddIndexColumn(Sequential0, "Sequence1", 1, 1, Int64.Type),
    SelfMerge = Table.NestedJoin(Sequential1, {"Sequence0"}, Sequential1, {"Sequence1"}, "Sequential1", JoinKind.LeftOuter),
    ExpandRename = Table.ExpandTableColumn(SelfMerge, "Sequential1", {"Numbers", "NumList"}, {"PrevNumbers", "PrevNumList"}),

bjulius

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZDBCoMwDIbfpWcDaapbd9x9ynCnUTxUEBScgvr+rNsgRtZCLuHnS77EOfV4DVuvMkVIBJiDwdA8VZM5dRtWz0kozckR0qApNNU/lP+SGGTA7PPuvh1njoqwLU5pBDIJyibVz0D7qnpuu2UTA79nVZEMT0GFues0dUJeGJZ+WXs/iqOF4wGzQDYh8pG8pDLNv2re", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, Won = _t]),
    DateType = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    Sort = Table.Sort(DateType,{{"Name", Order.Ascending}, {"Date", Order.Ascending}}),
    Group = Table.Group(Sort, {"Name", "Won"}, {{"AllData", each Table.AddIndexColumn( Table.Sort(_, {{"Date", 0}}), "rowindexinsubset",1,1 ), type table}}, GroupKind.Local),
    Expand = Table.ExpandTableColumn(Group, "AllData", {"Date", "rowindexinsubset"}, {"Date", "rowindexinsubset"}),
    Group2 = Table.Group(Expand, {"Name"}, {{"MaxStreak", each List.Max([rowindexinsubset]), type number}, {"AllData", each _, type ta

bjulius

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMtT1SsxTitWJVjLR9SrNAbMMDXX98svATCNTXZfUZKXYWAA=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )

bjulius

let
  Source = Excel.Workbook(File.Contents("C:\Users\brjul\Downloads\Subjects.xlsx"), null, true), 
  Sheet1_Sheet = Source{[Item = "Sheet1", Kind = "Sheet"]}[Data], 
  PromoteHeaders = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars = true]), 
  SplitByDelimToRows = Table.ExpandListColumn(
    Table.TransformColumns(
      PromoteHeaders, 
      {
        {
          "Subject", 

bjulius

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY7BDoIwDIZfhXDGhY7B8KiJBxJDTLyJHooMWcCZbBhf31Egemiytf36/VUV7tpHh0a7EU0YhdzXJmapBP+4mj2ax4CNcp3/imkGDGQW3iIPvt1ocdDo23LGQCaEFaahdu4LWAJbAg7TLdMs6zETy3apxk7ZaeRWS8xyMUOFVQuUzPoMUqJO2K+hM9LEPCeiVJ/govBPxRnArDq/3mMX7Fqr71O+lHLkUhB4tjo4ounxl0JwTtxFP2usP+oXAxJvu30B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Group A" = _t, Points = _t, NRR = _t, #"Group B" = _t, Points.1 = _t, NRR.1 = _t]),
    ReType = Table.TransformColumnTypes(Source,{{"Points", type number}, {"NRR", type number}, {"Points.1", type number}, {"NRR.1", type number}}),
    ATable = Table.FirstN( Table.Sort( Table.RemoveColumns( ReType, { "Group B", "Points.1", "NRR.1"}), {{"Points", Order.Descending}, {"NRR", Order.Descending}}), 2),
    BTable = Table.Sort( Table.FirstN( Table.Sort( Table.RemoveColumns( ReType, { "Group A", "Points", "NRR"}), {{"Points.1", Order.Descending}, {"NRR.1",