bjulius
bjulius
/ gist:52cdaf5e1fea03f65390076e06c4b4eb
Created
May 2, 2023 03:35
M Code for Dynamic Holiday Table Using Calendarific API
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| // Brian Julius' code to create a holiday table for specified years and country using Calendarific.com API | |
| // On line 6, enter the starting and ending years for which you want to retrieve holidays | |
| // On line 7, enter the ISO2 country code for the courntry for which you want to retrieve holidays | |
| // If you don't know your country's ISO2 code go to https://calendarific.com/ | |
| // Their API covers over 230 countries, 3300 states, and 100 languages | |
| // On line 13, replace the word "dummy" with your Calenderific API key (no quotes, just paste it over "dummy") | |
| // After the code runs, filter on primary_type to select the holidays you want to keep to feed into your Date table | |
| let |
bjulius
/ gist:70025ffbfc33e6611e8ba0ef520dd2cc
Created
May 3, 2023 15:49
Brian Julius M Code to Create Dynamic Holiday Table from Nager.at Public API
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| let | |
| fnHolidayTable = (StartYear as number, EndYear as number, ISO2Code as text) as table => | |
| let | |
| YearList = List.Numbers(StartYear, EndYear - StartYear + 1), | |
| YearTable = Table.FromList( | |
| YearList, | |
| Splitter.SplitByNothing(), | |
| {"HolYear"}, | |
| null, | |
| ExtraValues.Error |
bjulius
/ gist:67434f6ba6a56b80c379d6888442f159
Created
May 10, 2023 05:49
Excel BI Excel Challenge 190 – Brian Julius Solution
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| let | |
| Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], | |
| #"Add MaxPairs" = Table.AddColumn(Source, "Custom", each [ | |
| a = List.Transform( Text.Split([Numbers], ","), each Text.Trim(_)), | |
| b = List.Skip(a, 1 ) & {"null"}, | |
| c = Table.RemoveLastN( Table.FromColumns( {a, b}), 1), | |
| d = Table.AddColumn( c, "PairSum", each Number.From( [Column1] ) + Number.From( [Column2])), | |
| e = Table.AddColumn(d, "Pair", each Text.Combine( { [Column1], [Column2] }, ", ")), | |
| f = List.Max( e[PairSum] ), | |
| g = Table.SelectRows( e, each [PairSum] = f), |
bjulius
/ gist:b32ce13d7cea87abec1ab0cad62fd941
Created
June 3, 2023 18:00
ExcelBI Power Query Challenge 83 – Brian Julius Solution
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| let | |
| Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], | |
| AddQPrefix = Table.TransformColumns(Source, {{"Question No", each "Q" & Text.From(_, "en-US"), type text}}), | |
| AddCorrect = Table.AddColumn(AddQPrefix, "Correct", each if [Selected Answer] = [Right Answer] then "Y" else "N"), | |
| AddBincorrect = Table.AddColumn(AddCorrect, "BinCorrect", each if [Selected Answer] = [Right Answer] then 1 else 0), | |
| Group = Table.Group(AddBincorrect, {"Student"}, {{"QAnswered", each Table.RowCount(_), Int64.Type}, {"QCorrect", each List.Sum([BinCorrect]), type number}, {"All", each _, type table [Student=text, Question No=text, Right Answer=text, Selected Answer=text, Correct=text, BinCorrect=number]}}), | |
| #"Add%Score" = Table.AddColumn(Group, "%ageScore", each Value.Divide( [QCorrect], [QAnswered] ), Percentage.Type), | |
| Expand = Table.RemoveColumns( Table.ExpandTableColumn(#"Add%Score", "All", {"Question No", "Correct"}, {"Question No", "Correct"}), {"QAnswered", "QCorrect"}), | |
| Reorder = Table.ReorderColumns(Exp |
bjulius
/ gist:145a196d4ac94b13b42461a0cfa7aff9
Created
June 18, 2023 08:17
M and R Code for Dynamic Trend Stock Cards Using New Card Visual - Brian Julius
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| let | |
| Source = Web.BrowserContents("https://finance.yahoo.com/quote/NVDA/history?p=NVDA"), | |
| ExtractFromHTML = Html.Table(Source, {{"Column1", "TABLE.W\(100\%\).M\(0\) > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7), TABLE.W\(100\%\).M\(0\) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7), TABLE.W\(100\%\).M\(0\) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2), TABLE.W\(100\%\).M\(0\) > * > TR > TD[colspan=""7""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column2", "TABLE.W\(100\%\).M\(0\) > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(6), TABLE.W\(100\%\).M\(0\) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(6), TABLE.W\(100\%\).M\(0\) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2) + TD[colspan=""6""]:not([rowspa |
bjulius
/ gist:36967516297c007caa6cba5a0b6558cf
Created
June 23, 2023 19:29
Excel BI Challenge 223 - Brian Julius Solution
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| let | |
| Source = Table.AddIndexColumn( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "Index", 1, 1), | |
| ToRows = Table.ToRows( Table.RemoveColumns(Source, {"Sort Key", "Index"})), | |
| ToTable = Table.FromList(ToRows, Splitter.SplitByNothing(), null, null, ExtraValues.Error), | |
| Extract = Table.TransformColumns(ToTable, {"Column1", each Text.Combine(List.Transform(_, Text.From), ","), type text}), | |
| AddIndex = Table.AddIndexColumn(Extract, "Index1", 1, 1, Int64.Type), | |
| Join = Table.RemoveColumns( Table.Join( AddIndex, "Index1", Table.SelectColumns( Source, {"Sort Key", "Index"}), "Index", JoinKind.LeftOuter), "Index1"), | |
| SpliToRows = Table.ExpandListColumn(Table.TransformColumns(Join, {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"), | |
| Group = Table.Group(SpliToRows, {"Index", "Sort Key"}, {{"ListLength", each Table.RowCount(_), Int64.Type}, {"All", each [Column1], type list}}), |
bjulius
/ gist:61b872d57cf478b0a07df164d8ec9ce3
Created
July 12, 2023 01:09
Code Interpreter Python, M and R Code for Excel BI Power Query Challenge 85
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| PYTHON CODE | |
| *********** | |
| import pandas as pd | |
| # Load the dataset | |
| df = pd.read_csv('/mnt/data/PQ Challenge 85.csv') | |
| # Fill missing company names | |
| df['Company'].fillna(method='ffill', inplace=True) |
bjulius
/ gist:d97b8824c86996ae7007a2fd6e21af93
Created
July 12, 2023 03:50
Code Interpreter Python, M and R Code for Enterprise DNA Power Query Workout 10
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| PYTHON CODE | |
| *********** | |
| import pandas as pd | |
| # Load the data | |
| data = pd.read_csv('/mnt/data/Matthias Challenge.csv') | |
| # Convert 'Date' to datetime format | |
| data['Date'] = pd.to_datetime(data['Date']) |
bjulius
/ gist:c5bcb9f99d38bcb2f3ecdd45e43fa74b
Created
July 29, 2023 04:28
Excel BI Excel Challenge 248 Brian Julius Solution
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| let | |
| Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], | |
| LookupTable = Table.AddColumn( Source, "LookupTbl", each | |
| [ | |
| DateList = Table.FromList( List.Transform( { Number.From( #date(2023, 1, 1)) .. Number.From( #date(2023, 12, 31))}, each Date.From(_)), Splitter.SplitByNothing(), null, null, ExtraValues.Error), | |
| MonQ = Table.AddColumn(DateList, "MonthQ", each Date.ToText([Column1], "MMM") & "-Q" & Text.From( Date.QuarterOfYear([Column1])), type text), | |
| Base = Table.Distinct( Table.SelectColumns(MonQ, "MonthQ")), | |
| AddIndex = Table.AddIndexColumn(Base, "Index", 0, 1, type number), | |
| AddConstant = Table.AddColumn(AddIndex, "Constant", each 1, Int64.Type) |
bjulius
/ gist:2b0b25cfaaf12e80ff3213ecec0a437e
Created
July 29, 2023 18:28
Excel BI Power Query 99 Challenge - Brian Julius solution
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| let | |
| Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], | |
| Type = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Amount", Int64.Type}}), | |
| AddShortMon = Table.AddColumn(Type, "Month", each Text.Start( Date.MonthName( [Date] ), 3)), | |
| Group = Table.Group(AddShortMon, {"Month"}, {{"Min", each List.Min([Amount]), type nullable number}, {"Max", each List.Max([Amount]), type nullable number}, {"All", each _, type table [Date=nullable date, Amount=nullable number, Month=text]}}), | |
| Expand = Table.ExpandTableColumn(Group, "All", {"Date", "Amount"}, {"Date", "Amount"}), | |
| AddMinDate = Table.AddColumn(Expand, "MinDate", each if [Amount] = [Min] then [Date] else null, type text), | |
| AddMaxDate = Table.AddColumn(AddMinDate, "MaxDate", each if [Amount] = [Max] then [Date] else null, type text), | |
| Regroup = Table.Group(AddMaxDate, {"Month", "Min", "Max", "Amount"}, {{"MinDate2", each Text.Combine(List.Transform([MinDate], Text.From ), ", "), type nullable text},{"MaxDate2", each Text.Com |