bjulius

// The code below goes into a query named RegexFromAPI.  This returns the Regex from GPT4 based on the user described cleaning task.

// #GiveCredit4Code, thanks to Oscar Martinez Valero for profviding the structure of how to call
// GTP4 from within Power Query and parse the results that come back
// Source article: https://www.bibb.pro/post/ai-driven-data-analysis-chatgpt-power-query-integration-in-bi

let
  AnalyzeDataWithChatGPT = (RegexDescription as text) as text =>
    let
      apikey = Text.FromBinary(File.Contents("C:\temp\PQ_API_KEY.txt")), 

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddNumeric = Table.AddColumn(Source, "Numeric", each if [Grade] = "A+" then 4 else if [Grade] = "A" then 3 else if [Grade] = "B" then 2 else 1),
    AddYearMon = Table.AddColumn(AddNumeric, "YearMo", each (Date.Year([Date])  * 10000) + Date.Month([Date]) * 10),
    Group0 = Table.Group(AddYearMon, {"Agent-id", "YearMo"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Date=datetime, #"Agent-id"=text, Grade=text, Numeric=number, YearMo=number]}}),
    AddIncr = Table.RemoveColumns( Table.AddColumn(Group0, "Increment", each Table.AddIndexColumn( [All], "Incr", 1, 1)), "All"),
    Expand0 = Table.ExpandTableColumn(AddIncr, "Increment", {"Date", "Grade", "Numeric", "Incr"}, {"Date", "Grade", "Numeric", "Incr"}),
    FilterLast = Table.RemoveColumns( Table.SelectRows(Expand0, each ([Incr] = [Count])), {"Count", "Incr"}),
    Sort0 = Table.Buffer( Table.Sort(FilterLast,{{"Agent-id", Order.Ascending}, {"Date", Order.A

bjulius

Question Tables
Renewable energy resources (5), renewable energies (27), renewable resource (28), 81 renewable energy source (47)
Electric batteries (6), energy storage (18), 12 battery storage (52)
Photovoltaic cells (7), photovoltaic system (8)
Wind power (9), wind turbines (12), wind (24)
Solar power generation (15), 3 solar energy (32), solar power (48)
Algorithm (16), algorithms (37), genetic algorithms (19), genetic algorithm (45)
Hybrid energy system (20), hybrid renewable energy systems (21), hybrid renewable energies (41)
Related but not exactly the same terms 5
Optimization (3), multiobjective optimization (13), particle swarm optimization (PSO) (10)

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Lookup = Table.AddIndexColumn( Table.SelectColumns( Source, {"Color", "Code"} ), "Value", 0, 1),
    T1 = Table.SelectRows( Table.FromColumns( { Source[Color Bands], {1..9}}, {"Bands", "Index"} ), each [Bands] <> null),
    SplitToRows = Table.ExpandListColumn(Table.TransformColumns(T1, {{"Bands", Splitter.SplitTextByRepeatedLengths(2), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Bands"),
    Group = Table.Group(SplitToRows, {"Index"}, {{"MaxCounter", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Bands=nullable text, Index=number]}}),
    AddCount = Table.RemoveColumns( Table.AddColumn(Group, "All2", each Table.AddIndexColumn([All], "Counter", 1, 1)), "All"),
    Expand = Table.ExpandTableColumn(AddCount, "All2", {"Bands", "Counter"}, {"Bands", "Counter"}),
    Join = Table.Join( Expand, "Bands", Lookup, "Code"),
    AddAnswer = Table.AddColumn(Join, "Answer", each if [Count

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T1 = Table.SelectColumns( Source, {"Player", "Position", "Jersey # Wanted"}),
    T3 = Table.SelectColumns( Source, {"Taken", "Retired" }),
    T2 = Table.SelectColumns( Source, {"Numbers", "QB", "RB/WR/TE", "OL", "DL", "LB", "DB", "K/P" }),
    RemBlank = Table.SelectRows(T2, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    NumList = Table.AddColumn(RemBlank, "NumList", each [
        a = Text.Split( [Numbers], "–"),
        b = Number.From( a{0} ),
        c = Number.From( a{1} ),

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Vaccines = Table.AddIndexColumn( Table.Distinct( Table.SelectColumns( Table.SelectRows(Source, each [Notification Date] = null ), "Vaccine")), "CampNo", 1, 1),
    Names = Table.Sort( Table.RenameColumns(  Table.Distinct( Table.SelectColumns( Table.SelectRows(Source, each [Notification Date] <> null ), "Vaccine")), {"Vaccine", "Name"}), {"Name", Order.Ascending}),
    AddVaccine = Table.ExpandListColumn( Table.AddColumn(Vaccines, "Name", each Names[Name]), "Name"), 
    MatchTable = Table.TransformColumnTypes( Table.RenameColumns( Table.SelectRows( Table.FillDown( Table.ReplaceValue(Source,"Name",null,Replacer.ReplaceValue,{"Camp No"}), {"Camp No"}), each [Notification Date] <> null), {"Vaccine", "Name_"}), {"Camp No", Int64.Type}),
    Table.Join = Table.RemoveColumns( Table.Join(AddVaccine, {"Name", "CampNo"}, MatchTable, {"Name_", "Camp No"}, JoinKind.LeftOuter), {"Name_", "Camp No" }),
    AddNotified = Table.AddColumn(Table.Join, "No

bjulius

let
    Source = DataRaw,
    AddUnpivotFlag = Table.AddColumn(Source, "UnpivotFlag", each if Text.StartsWith( [Action], "EXT") then Text.From( [DATE1A] ) & "*" & Text.From( [DATE2A] ) else null),
    SplitFlagToRows = Table.ExpandListColumn(Table.TransformColumns(AddUnpivotFlag, {{"UnpivotFlag", Splitter.SplitTextByDelimiter("*", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "UnpivotFlag"),
    Group = Table.Group(SplitFlagToRows, {"ID"}, {{"All", each _, type table [ID=nullable text, PRICE=nullable number, QTY=nullable number, DATE1=nullable date, DATE2=nullable date, Action=nullable text, DATE1A=nullable date, DATE2A=nullable date, UnpivotFlag=nullable text]}}),
    AddIndex = Table.RemoveColumns( Table.AddColumn(Group, "Idx", each Table.AddIndexColumn( [All], "Index", 1, 1 )), "All"),
    AddActionz = Table.AddColumn( AddIndex, "Actionz", each Table.AddColumn( [Idx], "ACTIONz", each if [Index] = 1 then [Action] else null), Text.Type),
    AddDate

bjulius

let fnDateTable = (#date(2022, 1, 1), #date(2024, 12, 31, optional FYStartMonthNum as number, optional Holidays as list, optional WDStartNum as number ) as table =>
  let
    FYStartMonth = if List.Contains( {1..12}, FYStartMonthNum ) then FYStartMonthNum else 1,
    WDStart = if List.Contains( {0, 1}, WDStartNum ) then WDStartNum else 0,
    CurrentDate = Date.From(DateTime.FixedLocalNow()),
    DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    AddToday = if EndDate < CurrentDate then List.Combine( {Source, {CurrentDate}}) else Source,
    TableFromList = Table.FromList(AddToday, Splitter.SplitByNothing()),
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),

bjulius

let
 Source2 = Table.FromList(List.Numbers(2013, 11), Splitter.SplitByNothing(), {"Year"}, null, ExtraValues.Error),
 AddDay = Table.SelectRows( Table.AddColumn(Source2, "DayOfWeek", each Date.DayOfWeekName( #date( [Year], 10, 14))), each [DayOfWeek] = "Monday"),
 Answer = List.Max(AddDay[Year])
in
 Answer

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    Dupe = Table.DuplicateColumn(AddIndex, "Sentences", "Sentences2"),
    SplitToRows1 = Table.ExpandListColumn(Table.TransformColumns(Dupe, {{"Sentences2", Splitter.SplitTextByDelimiter(" """, QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Sentences2"),
    SplitToRows2 = Table.ExpandListColumn(Table.TransformColumns(SplitToRows1, {{"Sentences2", Splitter.SplitTextByDelimiter(""" ", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Sentences2"),
    IdentifyAddlSplits = Table.AddColumn(SplitToRows2, "Custom", each [
        a = [Sentences],
        b = Text.ToList( a ),
        c = List.Select( b, each _ = """" ),