Skip to content

Instantly share code, notes, and snippets.

View migueesc123's full-sized avatar

Miguel Escobar migueesc123

122 followers · 3 following
View GitHub Profile
@migueesc123
migueesc123 / Mac
Created March 17, 2023 18:27
Query
Value.ResourceExpression
Resource.Access
List.NonNullCount
List.MatchesAll
List.MatchesAny
List.Range
List.RemoveItems
List.ReplaceValue
List.FindText
@migueesc123
migueesc123 / reddit challenge?
Created September 26, 2021 03:20
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZHBCoJAEIZfRTwHqWughz1UlhYRxXaTDpMOsWS7sK5Cb595SKRNkmBO88PHN/+kqb2TGWguBU1Iso4DEjLfPk9Sew93pAdQN6vmRQHtbiMynqPQEWikjjttxnM8t5cxrFFx/aDz/lpWKkN6ApXz0gzzBmDLsTAyAFuYYMcKy1cPwBW2+buYOCGB7/khI10xCagLF1dUFtQoKhxfj/Gi7xKEbMOoeRGLOgmm28xSEvIBAccssPpJ4AM5+w95fgI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Split column by delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("="), {"Column1.1", "Column1.2"}),
Custom1 = Table.Buffer( #"Split column by delimiter"),
#"Added custom" = Table.AddColumn(Custom1, "Location", each if [Column1.1] = "Location" then [Column1.2] else null),
#"Added custom 1" = Table.AddColumn(#"Added custom", "Name", each if [Column1.1] = "Name" then [Column1.2] else null),
#"Filled down" = Table.FillDown(#"Added custom 1", {"Name", "Location"}),
#"Filtered rows" = Table.SelectRows(
@migueesc123
migueesc123 / gist:14f56fa40026e9aceb0d7d24eb2ddf36
Created September 8, 2021 07:51
Horizontally stacked sets
// Start
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMFTSUXIEYkMg2wBIG5mCKZ/M9IySzLx0hYLE5OzE9FSEjGdeSWpRZn6RQmhBelFiSqpSrA7YICOgnBMQW0DMQSCovDGQ7QwyBmoRVF1EsCvcJBT1JkC2C8hhpigOw7AeRZMpkO2KpAnJFbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OrderID = _t, Product = _t, #"Base Model" = _t, #"Add-on 1" = _t, #"Add-on 1 Desc" = _t, #"Add-on 2" = _t, #"Add-on 2 Desc" = _t])
in
Source
// Add-ons
let
Source = Start,
#"Demoted headers" = Table.DemoteHeaders(Source),
@migueesc123
migueesc123 / coolFx
Created August 31, 2021 11:39
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MLchJVTBU0lFyBGIXIHYGYlcgDgFiQyMgYQIijE2AhJEpiGumFKsD12kEVQnS4QbE7kAcjlAP0mBkDNEFJEyVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Row = _t, #"Cause 1" = _t, #"Cause 2" = _t, #"Cause 3" = _t, #"Cause 4" = _t, #"Cause 5" = _t, #"Cause Value 1" = _t, #"Cause Value 2" = _t, #"Cause Value 3" = _t, #"Cause Value 4" = _t, #"Cause Value 5" = _t]),
#"Added index 2" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
#"Removed columns" = Table.RemoveColumns(#"Added index 2", {"Row", "Index"}),
Custom = List.Count( Table.ColumnNames( #"Removed columns" ))/ 2,
#"Custom 1" = Table.ColumnNames(#"Removed columns"),
#"Converted to table" = Table.FromList(#"Custom 1", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added index" = Table.AddIndexColumn(#"Converted to table", "Index", 0, 1, Int64.Type),
#"Inserted modulo" = Ta
@migueesc123
migueesc123 / For Bernat
Created May 27, 2021 18:53
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLRN9E3MjAyUIrViVYyQggYKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type date}}),
Custom1 = type table [A = number, B = date],
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #"Changed Type", Custom1),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"A", "B"}, {"A.1", "B.1"})
in
#"Expanded Custom"
@migueesc123
migueesc123 / TableExpand
Created May 27, 2021 18:29
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLRN9E3MjAyUIrViVYyQggYKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type date}}),
Custom1 = type table [A = number, B = date, Custom = table [A = number, B = date]],
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #"Changed Type"),
Custom2 = Value.ReplaceType( #"Added Custom", Custom1),
#"Expanded Custom" = Table.ExpandTableColumn(Custom2, "Custom", {"A", "B"}, {"A.1", "B.1"})
in
#"Expanded Custom"
@migueesc123
migueesc123 / getCovidData
Created October 4, 2020 21:37
let
Source = Web.BrowserContents("https://github.com/CSSEGISandData/COVID-19/tree/master/csse_covid_19_data/csse_covid_19_daily_reports"),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", ".col-md-2.mr-3"}, {"Column2", ".js-navigation-open.link-gray-dark", each [Attributes][href]?}}, [RowSelector=".col-md-2.mr-3"]),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> ".gitignore" and [Column1] <> "README.md")),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","/blob/","/",Replacer.ReplaceText,{"Column2"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each Function.InvokeAfter( ()=> Table.PromoteHeaders( Csv.Document( Binary.Buffer( Web.Contents("https://raw.githubusercontent.com/", [RelativePath=[Column2]])))), #duration(0,0,0,1) )),
#"Replaced Value1" = Table.ReplaceValue(#"Added Custom",".csv","",Re
@migueesc123
migueesc123 / Incremental Refresh Yearly SP
Created January 19, 2020 01:59
(StartDate as datetime, EndDate as datetime) =>
let
start = Number.From (Date.From(StartDate)),
end = Number.From(Date.From(EndDate))-1,
Years = List.Distinct( List.Transform( {start..end}, each Text.From( Date.Year(Date.From(_))))),
Source = SharePoint.Files("https://powerbipanama.sharepoint.com/sites/Services", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each List.Contains( List.Transform( Years, (x)=> Text.Contains( [Folder Path],x) ), true)),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Excel.Workbook([Content], true)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
@migueesc123
migueesc123 / fxSCD2
Created January 11, 2020 20:30
let
Source = (x as table, y as text) as table =>
let
#"Changed Type" = Table.TransformColumnTypes(x,{{y, type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{y, Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1),
#"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {y}, {"M"}),
#"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "To", each Date.AddDays([M] ,-1)),
@migueesc123
migueesc123 / Old School Combine with Custom Error
Created October 28, 2019 07:38
(myFolderPath as text) as table =>
let
Source = Folder.Files(myFolderPath),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
Content = #"Removed Other Columns"[Content],
Custom1 = Binary.Combine(Content),
Custom2 = Csv.Document (Custom1)
in
try Custom2 otherwise error [Reason="Transformation Exception", Message="The data that you're trying to combine does not appear to be a flat file", Detail="Make sure that you're only combining csv, txt or other flat files"]