cbaragao’s gists

cbaragao / fnGetLuminosity.pq

Created August 19, 2023 20:44

Get the luminosity of a hex color with Power Query

	// Calculate luminosity based on hex value
	(HEX as text)=>

	let
	// Split strings into list by each character, convert to upper, remove hash
	Source = if HEX <> "" then Text.Upper(Text.AfterDelimiter(Text.From(HEX), "#")) else "#000000",

	// Split the Source by each character
	SplitHex = Splitter.SplitTextByRepeatedLengths(1)(Source),

cbaragao / fnGetHue.pq

Created August 19, 2023 22:15

Get the hue of a hex value using Power Query

	(HEX as text)=>

	let
	// Split strings into list by each character, convert to upper, remove hash
	Source = if HEX <> "" then Text.Upper(Text.AfterDelimiter(Text.From(HEX), "#")) else "#000000",

	// Split the Source by each character
	SplitHex = Splitter.SplitTextByRepeatedLengths(1)(Source),

	GetHue = (hexlist)=>

cbaragao / fnGetRGBFromHex.pq

Created August 19, 2023 22:16

Get the RGB value from a hex value

	// Calculate RGB based on hex value
	(HEX as text)=>

	let
	// Split strings into list by each character, convert to upper, remove hash
	Source = if HEX <> "" then Text.Upper(Text.AfterDelimiter(Text.From(HEX), "#")) else "#000000",

	// Split the Source by each character
	SplitHex = Splitter.SplitTextByRepeatedLengths(1)(Source),

cbaragao / ExcelBI106Solution.pq

Created August 20, 2023 12:26

Excel BI Power Query Challenge 106

	let
	Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
	#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
	#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
	#"Promoted Headers",
	{"Class"},
	"Attribute",
	"Value"
	),
	#"Extracted Text Before Delimiter" = Table.TransformColumns(

cbaragao / fnGetColorFade.pq

Created August 20, 2023 19:46

Generate a Color Fade table in Power Query and fade your colors with DAX

	(HEX as text) =>
	let
	// Split strings into list by each character, convert to upper, remove hash
	Source = if HEX <> "" then Text.Upper(Text.AfterDelimiter(Text.From(HEX), "#")) else "#000000",
	// Split the Source by each character
	SplitHex = Splitter.SplitTextByRepeatedLengths(1)(Source),
	GetColor = (hexlist) =>
	let
	// Build the RGB list
	RGB = List.Combine({{"0" .. "9"}, {"A" .. "F"}}),

cbaragao / excelbipqchallenge107

Created August 26, 2023 15:34

Excel BI PQ Challenge #107

	let

	Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],

	#"Added Coalesce" = Table.AddColumn(Source, "Coalesce", each [Column2] ?? [Column3] ?? [Column1]),

	#"Filtered Rows" = Table.SelectRows(#"Added Coalesce", each ([Coalesce] <> null)),

	#"Added Date" = Table.AddColumn(

cbaragao / fnCheckColorBlindness.pq

Created September 10, 2023 03:27

A function in Power Query to provide hex codes in order to simulate color blindness

	// Calculate RGB based on hex value
	(HEX as text)=>

	let
	// Split strings into list by each character, convert to upper, remove hash
	Source = if HEX <> "" then Text.Upper(Text.AfterDelimiter(Text.From(HEX), "#")) else "#000000",

	// Split the Source by each character
	SplitHex = Splitter.SplitTextByRepeatedLengths(1)(Source),

cbaragao / SQL_Lambdas

Created September 29, 2023 00:06

	// Structure CREATE TABLE syntax.

	SQL_CREATE_TABLE = LAMBDA(table_name,range, "CREATE TABLE " & table_name & " (" & CHAR(10) & REPT(" ", 5) & TEXTJOIN(", " & CHAR(10) & REPT(" ", 5), TRUE,BYROW(range,LAMBDA(r, TEXTJOIN(" ", TRUE,r)))) & CHAR(10) & ");")

	// Structure IN clause with numbers.

	SQL_IN_CLAUSE_NUM = LAMBDA(range,CONCAT("IN (",TEXTJOIN(", ",TRUE,range), ")"))

	// Structure IN clause with text.

cbaragao / DaxHexMapExample.txt

Last active October 28, 2023 14:50

DAX Hex Map Example

	SVG :=

	// If a color is in the dataset, make it red
	VAR _selected_color = "#FF0000"

	// If it is not, make it gray
	VAR _not_selected_color = "#A5A5A5"

	//Set up the SVG header
	VAR _header = "data:image/svg+xml;utf8,<svg width=""1280"" height=""720"" xmlns=""http://www.w3.org/2000/svg"" xmlns:xlink=""http://www.w3.org/1999/xlink"" overflow=""hidden"">

cbaragao / UK_Constituencies_HexMap.txt

Created October 30, 2023 02:25

	UK_SVG =

	// If a color is in the dataset, make it red
	VAR _selected_color = "#FF0000"

	// If it is not, make it gray
	VAR _not_selected_color = "#A5A5A5"

	//Set up the SVG header
	VAR _header = "data:image/svg+xml;utf8,<svg xmlns=""http://www.w3.org/2000/svg"" class=""hexmap-map"" version=""1.1"" overflow=""visible"" viewBox=""0 0 1746 1746"" style=""max-width:100%;"" preserveAspectRatio=""xMinYMin meet"" vector-effect=""non-scaling-stroke"" width=""1746"" height=""1746"">

Chris Aragao cbaragao