bjulius

let
    Source = Web.BrowserContents("https://www.w3schools.com/cssref/css_colors.php"),
    ExtractFromHTML = Html.Table(Source, {{"Column1", ".colornamespan *"}, {"Column2", ".colorhexspan *"}, {"Column3", ".linktocolormixerdiv *"}, {"Column4", ".linktocolorpickerdiv *"}}, [RowSelector=".colorbox"]),
    RemoveCols = Table.RemoveColumns(ExtractFromHTML,{"Column3", "Column4"}),
    RenameCols = Table.RenameColumns(RemoveCols,{{"Column1", "NamedColor"}, {"Column2", "HexCode"}}),
    TransparentRow = #table({"NamedColor", "HexCode"}, {{"Transparent", "#FFFFFF00"}}),
    AppendTransparent = Table.Combine( {RenameCols, TransparentRow}),
    Addndex = Table.AddIndexColumn(AppendTransparent, "Index", 1, 1, Int64.Type),
    Reorder = Table.ReorderColumns(Addndex,{"Index", "NamedColor", "HexCode"}),
    ChangeTypes = Table.TransformColumnTypes(Reorder,{{"NamedColor", type text}, {"HexCode", type text}}) 

bjulius

let
    // Your GitHub token for authentication
    github_token = "Your Github Token Here",
    
    // GitHub API URL for listing your gists
    api_url = "https://api.github.com/gists",
    
    // Make the API request
    Source = Json.Document(Web.Contents(api_url, 
        [Headers=[#"Authorization"="token " & github_token]])),

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    SplitToRows = Table.ExpandListColumn(Table.TransformColumns(AddIndex, {{"Number", Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"}), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Number"),
    AddTextToNum = Table.AddColumn(SplitToRows, "Custom", each if [Number] = "Four" then 4 else if [Number] = "Six" then 6 else if [Number] = "Two" then 2 else if [Number] = "One" then 1 else if [Number] = "Seven" then 7 else if [Number] = "Five" then 5 else if [Number] = "Zero" then 0 else if [Number] = "Nine" then 9 else if [Number] = "Three" then 3 else if [Number] = "Eight" then 8 else null, type number),
    Group = Table.Group(AddTextToNum, {"Index"}, {{"All", each Table.Sort( _, {"Custom", Order.Descending}), type table [Number=nullable text, Index=number, Custom=number]}}),
    RemCol = Table.TransformColumns( Table.R

bjulius

let
    Source = Excel.Workbook(File.Contents("C:\Users\brjul\Downloads\PQ_Challenge_127.xlsx"), null, true),
    Expand = Table.PromoteHeaders( Table.SelectColumns( Table.ExpandTableColumn(Source, "Data", {"Column1", "Column2", "Column4"}, {"Column1", "Column2", "Column4"}), {"Column1", "Column2", "Column4"})),
    T1 = Table.SelectColumns( Expand, {"Name", "Date of Birth"}),
    T1Final = Table.SelectRows(T1, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    T2 = Table.SelectColumns(Expand, "Zodiac Signs"),
    Split = Table.SplitColumn(T2, "Zodiac Signs", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Zodiac Signs", "Start"}),
    TrimParens = Table.TransformColumns(Split, {{"Start", each Text.Trim(_, {"(", ")"} ),type text}}),
    Split2 = Table.SplitColumn(TrimParens, "Start", Splitter.SplitTextByEachDelimiter({" – "}, QuoteStyle.Csv, false), {"Start", "End"}),
    ReType = Table.TransformColumnTypes(Split2,{{"Zodiac Signs", type text}, {"Start",

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="rng"]}[Content],
    Transpose = Table.Transpose(Source),
    Split = Table.ExpandListColumn(Table.TransformColumns(Transpose, {{"Column2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column2"),
    CreatList = Table.RemoveColumns( Table.AddColumn(Split, "Custom", each List.Repeat({"1"}, Number.From( [Column2])) & {""}), "Column2"),
    Group = Table.Group(CreatList, {"Column1"}, {{"All", each [Custom], type list}}),
    Expand = Table.ExpandListColumn( Table.ExpandListColumn(Group, "All"), "All"),
    Group2 = Table.Group(Expand, {"Column1"}, {{"All", each _, type table [Column1=text, All=text]}, {"Count", each Table.RowCount(_), Int64.Type}}),
    AddIndex = Table.RemoveColumns( Table.AddColumn(Group2, "Index", each Table.AddIndexColumn( [All], "Index", 1, 1)), "All"),
    Expand2 = Table.RemoveColumns( Table.SelectRows( Table.ExpandTableColumn(AddIndex, "Index", 

bjulius

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WClGK1YlWCtLx1QkGs9x0QOxwnVAwLxzIC9Fxg7J9wXQwWB6iD6gLJBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Days = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Index", "Days", "Days - Copy"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated Column", {{"Days - Copy", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Days - Copy"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Days - Copy", Text.Trim, type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Trimmed Text",{{"Days - Copy", "Key"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Key"}, #"Days Reference", {"

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    RemoveLetters = Table.AddColumn(Source, "Custom", each Text.Remove([Visits Tracker], {"A".."z","-"})),
    Trim = Table.TransformColumns(RemoveLetters,{{"Custom", Text.Trim, type text}}),
    SplitSpacetoRows = Table.ExpandListColumn(Table.TransformColumns(Trim, {{"Custom", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
    Filter = Table.SelectRows(SplitSpacetoRows, each ([Custom] <> "")),
    SplitSlashtoRows = Table.ExpandListColumn(Table.TransformColumns(Filter, {{"Custom", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
    ReType = Table.TransformColumnTypes(SplitSlashtoRows,{{"Custom", Int64.Type}}),
    Add0Index = Table.AddIndexColumn(ReType, "Index", 0, 1, Int64.Type),
    Modulo2 = Table.AddColumn(Add0Index, "Modulo", each

bjulius

let
    Source = Table.RemoveColumns( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "Range Difference"),
    AddSortOrder = Table.AddIndexColumn(Source, "SortOrder", 0, 1, Int64.Type),
    Table2 = Table2,
    Unpivot = Table.TransformColumns( Table.UnpivotOtherColumns(Table2, {"Grade"}, "Year", "Value"), {"Year", each Text.Select(_, {"0".."9"})}),
    NamesGrades = Table.SelectColumns( AddSortOrder, {"Full Name", "Job Grade ", "Salary"}),
    Join = Table.Join(NamesGrades, "Job Grade ", Unpivot, "Grade", JoinKind.LeftOuter),
    AddDiff = Table.RemoveColumns( Table.AddColumn(Join, "Difference", each [Value] - [Salary]), {"Grade", "Value"}),
    AddNonNegDiff = Table.TransformColumnTypes( Table.SelectRows(AddDiff, each [Difference] >= 0), {"Year", Int64.Type}),
    Group = Table.Group(AddNonNegDiff, {"Full Name"}, {{"MinDiffNonNeg", each List.Min([Difference]), type number}}),

bjulius

let
    Source = Table.DuplicateColumn( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "SA IDs", "ID"),
    #"Sp;it1-6" = Table.SplitColumn(Source, "ID", Splitter.SplitTextByPositions({0, 6}, false), {"Date", "ID.2"}),
    #"Split7-10" = Table.SplitColumn(#"Sp;it1-6", "ID.2", Splitter.SplitTextByPositions({0, 4}, false), {"SSSS", "ID.2.2"}),
    Split11 = Table.SplitColumn(#"Split7-10", "ID.2.2", Splitter.SplitTextByPositions({0, 1}, false), {"Citizen", "ID.2.2.2"}),
    Split12 = Table.SplitColumn(Split11, "ID.2.2.2", Splitter.SplitTextByPositions({0, 1}, false), {"Rand", "Checksum"}),
    Split13 = Table.SplitColumn(Split12, "Date", Splitter.SplitTextByRepeatedLengths(2), {"Year", "Month", "Day"}),
    TypeInt64 = Table.TransformColumnTypes(Split13,{{"Year", Int64.Type}, {"Month", Int64.Type}, {"Day", Int64.Type}, {"SSSS", Int64.Type}, {"Citizen", Int64.Type}, {"Rand", Int64.Type}, {"Checksum", Int64.Type}}),
    AddtoYear = Table.TransformColumns(TypeInt64, {{"Year", each _ + 1900, type number}}),
    

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ToRecord = Table.CombineColumnsToRecord(Source, "Value", Table.ColumnNames(Source)),
    ToTable = Table.TransformColumns(ToRecord, {"Value", each Record.ToTable(_)}),
    AddOriginalRow = Table.AddIndexColumn(ToTable, "OriginalRow", 1, 1, Int64.Type),
    Expand = Table.ExpandTableColumn(AddOriginalRow, "Value", {"Name", "Value"}, {"Name", "Values"}),
    AddIsPopulated = Table.AddColumn(Expand, "IsPopulated", each if [Values] = null then 0 else 1),
    AddIndex = Table.AddIndexColumn(AddIsPopulated, "Index", 0, 1, Int64.Type),
    AddRunningTotal = Table.AddColumn(AddIndex, "RunningTotal", each List.Sum( List.FirstN( AddIndex[IsPopulated], [Index] + 1 ))),
    AddRTPrevRow = Table.AddColumn(AddIndex, "RTPrevRow", each List.Sum( List.FirstN( AddIndex[IsPopulated], [Index] ))),