The power-query from alendaris

(url as text) =>

Implement function to pull functions from Git Repository

AssertTable is filtering too early

AssertTable currently filters before applying the test, which means tests on data that will be removed will always fail.

power-query/functions/Assert.pq

Lines 477 to 485 in b8af1b3

    
           // Runs an Assertion Check on an entire table and returns only the rows where there is an issue 
        
           AssertTable = (inputTable as table, functionToApply as function, optional columnsToInclude as list) => 
        
               let 
        
                   indexedTable = Table.AddIndexColumn(inputTable, "Index", 2, 1, Int64.Type), 
        
                   selectedColumns = if (columnsToInclude = null) then Table.ReorderColumns(indexedTable, {"Index"} & Table.ColumnNames(inputTable)) else Table.SelectColumns(indexedTable, {"Index"} & columnsToInclude), 
        
                   transformedTable = Table.AddColumn(selectedColumns, "_Assert", functionToApply), 
        
                   filteredTable = Table.SelectRowsWithErrors(transformedTable) 
        
               in  
        
                   filteredTable,

Swap selectedColumns and transformedTable
Update the remaining lines

Add documentation metadata to Luminosity.pq

power-query/functions/Luminosity.pq

Lines 1 to 9 in 543c2e6

    
           (color as text, optional gamma as number) => 
        
           let 
        
               gamma_use = if gamma = null then 2.2 else gamma, 
        
               luminosity =  
        
                   0.2126 * Number.Power(Expression.Evaluate("0x" & Text.Middle(color, 1, 2))/255,gamma_use) + // Red 
        
                   0.7152 * Number.Power(Expression.Evaluate("0x" & Text.Middle(color, 3, 2))/255,gamma_use) + // Green 
        
                   0.0722 * Number.Power(Expression.Evaluate("0x" & Text.Middle(color, 5, 2))/255,gamma_use),  // Blue 
        
           in 
        
               luminosity

Rework ValueToMCode.pq to use the actual recursion operator

Don’t know what I was thinking or why I kept missing the recursion operator reading through the M language specification. Frankly, it’s a miracle that the function even works as it is currently.

ValueToMCode - Hardcoded function name is not sufficient to identify the function in cases where the query name is different

Test existing code from #44

power-query/functions/ValueToMCode.pq

Line 4 in 455ea25

ThisFunctionName = "ValueToMCode",

Add documentation metadata to Library Function Source Code.pq

power-query/functions/Library Function Source Code.pq

Lines 1 to 11 in 543c2e6

    
           (LibraryName as any, functionName as text) => 
        
           let 
        
               library =  
        
                   if Value.Type(LibraryName) = Text.Type then 
        
                       Expression.Evaluate(Expression.Identifier(LibraryName), #shared) 
        
                   else 
        
                       LibraryName, 
        
               func = Record.Field(library,functionName), 
        
               code = "// " & functionName & "#(cr)#(lf)#(cr)#(lf)" & Value.Metadata(Value.Type(func))[Source Code] 
        
           in 
        
               code

Add documentation metadata to Compress.pq

power-query/functions/Compress.pq

Lines 1 to 5 in 543c2e6

    
           (uncompressedText as text) as text => 
        
           let 
        
               Source = Text.From(Binary.Compress(Text.ToBinary(uncompressedText),Compression.Deflate)) 
        
           in 
        
               Source

ValueToMCode needs Documentation Metadata

Update GithubRepository.pq to output as a record type value when not called with a URL

Add documentation metadata to ContrastingColor.pq

power-query/functions/ContrastingColor.pq

Lines 1 to 17 in 543c2e6

    
           (background_color as text, optional dark_text as text, optional light_text as text, optional gamma as number) => 
        
           let 
        
               dark_text_use = if dark_text = null then "#000000" else dark_text, 
        
               light_text_use = if light_text = null then "#FFFFFF" else light_text, 
        
               gamma_use = if gamma = null then 2.2 else gamma, 
        
               luminosity = (color as text, optional gamma as number) => 
        
                   let 
        
                       gamma_use = if gamma = null then 2.2 else gamma, 
        
                       luminosity =  
        
                           0.2126 * Number.Power(Expression.Evaluate("0x" & Text.Middle(color, 1, 2))/255,gamma_use) + // Red 
        
                           0.7152 * Number.Power(Expression.Evaluate("0x" & Text.Middle(color, 3, 2))/255,gamma_use) + // Green 
        
                           0.0722 * Number.Power(Expression.Evaluate("0x" & Text.Middle(color, 5, 2))/255,gamma_use)  // Blue 
        
                   in 
        
                       luminosity, 
        
               font_color = if luminosity(background_color, gamma_use)>0.5 then dark_text_use else light_text_use 
        
           in 
        
               font_color

Add documentation metadata to Unzip.pq

power-query/functions/Unzip.pq

Lines 1 to 101 in 543c2e6

    
           // Unzip 
        
           // Pulled from: https://community.powerbi.com/t5/Power-Query/How-to-connect-Azure-DevOps-REST-API-in-to-power-bi/m-p/895318/highlight/true#M30599 
        
           // Microsoft Power BI Community Forums Member artemus :: https://community.powerbi.com/t5/user/viewprofilepage/user-id/62590 
        
           (ZIPFile) =>  
        
           let 
        
               ushort = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian), 
        
               uint = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian), 
        
               EDOCfn = BinaryFormat.Record([ 
        
                   ZipContent = BinaryFormat.Binary(Binary.Length(ZIPFile) - 22), 
        
                   Magic = uint, 
        
                   DiskNum = ushort, 
        
                   CDirectoryDiskId = ushort, 
        
                   CDirectoryRecordCountOnDisk = ushort, 
        
                   CDirectoryRecordCount = ushort, 
        
                   SizeOfCentralDirectory = uint, 
        
                   CentralDirectoryOffset = uint, 
        
                   CommendLength = ushort 
        
               ]), 
        
               EDOC = EDOCfn(ZIPFile), 
        
               BeforeCentralDirectory = BinaryFormat.Binary(EDOC[CentralDirectoryOffset]), 
        
               CentralDirectory = BinaryFormat.Length(BinaryFormat.Record( 
        
                   [ 
        
                       ZipContent = BeforeCentralDirectory, 
        
                       Items = BinaryFormat.List(BinaryFormat.Record( 
        
                           [ 
        
                               Magic = uint, 
        
                               CurrentVersion = ushort, 
        
                               MinVersion = ushort, 
        
                               Flags = ushort, 
        
                               CompressionMethod = ushort, 
        
                               FileModificationTime = ushort, 
        
                               FileModificationDate = ushort, 
        
                               CRC32 = uint, 
        
                               BinarySize = uint, 
        
                               FileSize   = uint, 
        
                               FileInfo = BinaryFormat.Choice( 
        
                               BinaryFormat.Record( 
        
                                   [ 
        
                                       Len = ushort, 
        
                                       FieldsLen = ushort, 
        
                                       FileCommentLength = ushort, 
        
                                       Disk = ushort, 
        
                                       InternalFileAttr = ushort, 
        
                                       ExternalAttr = uint, 
        
                                       PosOfFileHeader = uint 
        
                                   ]), 
        
                               (fileInfo) => BinaryFormat.Record( 
        
                                   [ 
        
                                       FileName = BinaryFormat.Text(fileInfo[Len], TextEncoding.Ascii), 
        
                                       Fields = BinaryFormat.Binary(fileInfo[FieldsLen]), 
        
                                       FileComment = BinaryFormat.Text(fileInfo[FileCommentLength], TextEncoding.Ascii), 
        
                                       Disk = BinaryFormat.Transform(BinaryFormat.Null, each fileInfo[Disk]), 
        
                                       InternalFileAttr = BinaryFormat.Transform(BinaryFormat.Null, each fileInfo[Disk]), 
        
                                       ExternalAttr = BinaryFormat.Transform(BinaryFormat.Null, each fileInfo[InternalFileAttr]), 
        
                                       PosOfFileHeader = BinaryFormat.Transform(BinaryFormat.Null, each fileInfo[PosOfFileHeader]) 
        
                                   ]) 
        
                               ) 
        
                           ]),  
        
                           EDOC[CDirectoryRecordCount] 
        
                       ) 
        
                   ]),  
        
                   EDOC[CentralDirectoryOffset] + EDOC[SizeOfCentralDirectory]),   
        
               Contents = List.Transform( 
        
                   CentralDirectory(ZIPFile)[Items], 
        
                       (cdEntry) =>  
        
                           let 
        
                               ZipEntry = BinaryFormat.Record( 
        
                               [ 
        
                                   PreviousData = BinaryFormat.Binary(cdEntry[FileInfo][PosOfFileHeader]),  
        
                                   Magic = uint, 
        
                                   ZipVersion = ushort, 
        
                                   ZipFlags = ushort, 
        
                                   CompressionMethod = ushort, 
        
                                   FileModificationTime = ushort, 
        
                                   FileModificationDate = ushort, 
        
                                   CRC32 = uint,  
        
                                   BinarySize = uint, 
        
                                   FileSize   = uint, 
        
                                   FileName = BinaryFormat.Choice( 
        
                                       BinaryFormat.Record( 
        
                                           [ 
        
                                               Len = ushort, 
        
                                               FieldsLen = ushort 
        
                                           ]), 
        
                                       (fileInfo) => BinaryFormat.Record( 
        
                                           [ 
        
                                               FileName = BinaryFormat.Text(fileInfo[Len], TextEncoding.Ascii), 
        
                                               Fields = BinaryFormat.Binary(fileInfo[FieldsLen]) 
        
                                           ])  
        
                                   ), 
        
                                   FileContent = BinaryFormat.Transform( 
        
                                       BinaryFormat.Binary(cdEntry[BinarySize]),  
        
                                       each Binary.Decompress(_, Compression.Deflate) 
        
                                   ) 
        
                               ])(ZIPFile) 
        
                           in 
        
                               [FileName=ZipEntry[FileName][FileName], Content=ZipEntry[FileContent]] 
        
               ) 
        
           in 
        
               Contents

Drop Switch function

Redo if statement to catch all types
Drop the switch function

https://github.com/alendaris/power-query/blob/18-drop-switch-function/functions/ValueToMCode.pq#L47-L66

power-query/functions/ValueToMCode.pq

Lines 47 to 66 in 0419189

    
           Fail = (value as any) as text => "Type Unrecognized", 
        
           Switch = (Expression as any, Values as list, Results as list, optional Else as any) => 
        
               try Results{List.PositionOf(Values, Expression)}  
        
               otherwise 
        
                   if Else = null then Fail 
        
                   else Else, 
        
           ToMCode = (value as any,optional indent as number) as any => 
        
               let  
        
                   ParseFunc = Switch(Value.Type(Source), 
        
                   {type null,type text,type number}, 
        
                   {NullToMCode,TextToMCode,NumberToMCode}), 
        
                   Return =  
        
                       if Value.Is(value,Table.Type) then TableToMCode(value,indent)  
        
                       else if Value.Is(value,Record.Type) then RecordToMCode(value,indent) 
        
                       else if Value.Is(value,List.Type) then ListToMCode(value,indent) 
        
                       else ParseFunc(Source,indent) 
        
               in 
        
                   Return,

Create Luminance and Color Contrast functions

Luminance can be created from luminance steps used in ContrastingColor.
Color Contrast is (luminance(lighter) +.05)/(luminance(darker) +.05)

FunctionToMCode output is quoted

Output is quoted.. Only the Deflated function code should be quoted.

power-query/functions/ValueToMCode.pq

Lines 60 to 69 in 3e1fb21

    
           FunctionToMCode = (value as function) as text => 
        
               if Record.HasFields(Value.Metadata(Value.Type(value)),"Source Code") then 
        
                   let   
        
                       Source = Value.Metadata(Value.Type(value))[Source Code], 
        
                       Deflated = Text.From(Binary.Compress(Text.ToBinary(Source),Compression.Deflate)), 
        
                       Reinflate = "Text.FromBinary(Binary.Decompress(Binary.FromText(" & Deflated & "),Compression.Deflate))" 
        
                   in   
        
                       TextToMCode(Reinflate) 
        
               else 
        
                   TextToMCode("/* Function without Source Code Metadata */null"),

ColorContrastRatio is not displaying examples

power-query/functions/ColorContrastRatio.pq

Lines 21 to 50 in 4397dec

    
           ExampleParameter1 = "#FFCC72", 
        
           ExampleParameter2 = "#000000", 
        
           ExampleResult = func(ExampleParameter1,ExampleParameter2), 
        
           fntype = type function 
        
               ( 
        
                   lighter_color as (type text meta [ 
        
                       Documentation.FieldCaption = "Color 1", 
        
                       Documentation.FieldDescription = "One of two colors to compare in the contrast ratio calculation.", 
        
                       Documentation.SampleValues = {ExampleParameter1} 
        
                   ]), 
        
                   darker_color as (type text meta [ 
        
                       Documentation.FieldCaption = "Color 2", 
        
                       Documentation.FieldDescription = "One of two colors to compare in the contrast ratio calculation.", 
        
                       Documentation.SampleValues = {ExampleParameter2} 
        
                   ]) 
        
               ) as text meta 
        
                   [ 
        
                       Documentation.Name = "ColorContrastRatio", 
        
                       Documentation.LongDescription = "This function calculates the Color Contrast Ratio between the lighter color and the darker color.", 
        
                       Documentation.Examples = 
        
                       { 
        
                           [ 
        
                               Description = "Determine the color contrast ratio between #FFCC72 and #000000.", 
        
                               Code = "ColorContrastRatio(""#FFCC72"", ""#000000"")", 
        
                               Result = ExampleResult 
        
                           ] 
        
                       } 
        
                   ]

Line 37 may be at issue with the incorrect type for the function return (function should return numeric):

power-query/functions/ColorContrastRatio.pq

Line 37 in 4397dec

) as text meta

Add documentation metadata to Load Function Library - Local Filesystem.pq

power-query/functions/Load Function Library - Local Filesystem.pq

Lines 1 to 23 in 543c2e6

    
           (FolderPath as text) => 
        
           let 
        
               Source = Folder.Files(FolderPath), 
        
               #"Removed Other Columns" = Table.SelectColumns(Source, {"Content", "Name"}), 
        
               #"Remove .pq" = Table.ReplaceValue(#"Removed Other Columns", ".pq", "", Replacer.ReplaceText, {"Name"}), 
        
               #"Pull Source Code" = Table.AddColumn(#"Remove .pq", "Source Code", each Text.Combine(Lines.FromBinary([Content]), "#(lf)")), 
        
               #"Generate Function" = Table.AddColumn(#"Pull Source Code", "Function from File", each Expression.Evaluate([Source Code], #shared)), 
        
               #"Original Function Type" = Table.AddColumn(#"Generate Function", "Original Function Type", each Value.Type([Function from File])), 
        
               #"Add Source Code" = Table.AddColumn(#"Original Function Type", "Function Type Metadata with Source Code Added", each Record.AddField(Value.Metadata([Original Function Type]), "Source Code", [Source Code])), 
        
               #"Regenerate Function" = Table.AddColumn(#"Add Source Code", "Value", each Value.ReplaceType([Function from File], Value.ReplaceMetadata([Original Function Type], [Function Type Metadata with Source Code Added]))), 
        
               #"Removed Other Columns1" = Table.SelectColumns(#"Regenerate Function", {"Name", "Value"}), 
        
               newrecord = Record.FromTable(#"Removed Other Columns1"), 
        
               Library = if Record.HasFields(newrecord, {"Library Source Code"}) then 
        
                   [ 
        
                       Library = newrecord, 
        
                       Source Code = newrecord[Library Source Code](newrecord) 
        
                   ] 
        
                   else 
        
                   [ 
        
                       Library = newrecord 
        
                   ] 
        
           in 
        
               Library

Add documentation metadata to Library Source Code.pq

power-query/functions/Library Source Code.pq

Lines 1 to 15 in 543c2e6

    
           (libraryName as any) => 
        
           let 
        
               SourceCodeNamePrefix = "", 
        
               library =  
        
                   if Value.Type(libraryName) = Text.Type then 
        
                       Expression.Evaluate(Expression.Identifier(libraryName), #shared) 
        
                   else 
        
                       libraryName, 
        
               functionSourceCode = library[Library Function Source Code], 
        
               funcs = Table.RemoveColumns(Record.ToTable(library), "Value"), 
        
               codeTable = Table.AddColumn(funcs, "Value", each functionSourceCode(libraryName, [Name])), 
        
               #"Replaced Value" = Table.ReplaceValue(codeTable, each [Name], each SourceCodeNamePrefix & [Name], Replacer.ReplaceText, {"Name"}), 
        
               code = Record.FromTable(#"Replaced Value") 
        
           in 
        
               code

ToMCode sub function should be main-level code

power-query/functions/ValueToMCode.pq

Lines 55 to 68 in 0419189

    
           ToMCode = (value as any,optional indent as number) as any => 
        
               let  
        
                   ParseFunc = Switch(Value.Type(Source), 
        
                   {type null,type text,type number}, 
        
                   {NullToMCode,TextToMCode,NumberToMCode}), 
        
                   Return =  
        
                       if Value.Is(value,Table.Type) then TableToMCode(value,indent)  
        
                       else if Value.Is(value,Record.Type) then RecordToMCode(value,indent) 
        
                       else if Value.Is(value,List.Type) then ListToMCode(value,indent) 
        
                       else ParseFunc(Source,indent) 
        
               in 
        
                   Return, 
        
           Return = ToMCode(Source)

Add documentation metadata to Decompress.pq

power-query/functions/Decompress.pq

Lines 1 to 5 in 543c2e6

    
           (compressedText as text) as text => 
        
           let 
        
               Source = Text.FromBinary(Binary.Decompress(Binary.FromText(compressedText), Compression.Deflate)) 
        
           in 
        
               Source

Create TableDataQualityProfile.pq

Catch up README documentation to the actual contents of the repository

Implement function to cleanly rename all table columns

Implement a function that will split field names on camelCase transitions and replace underscores with whitespace

RecordToMCode does not properly escape illegal field names

power-query/functions/ValueToMCode.pq

Line 45 in b4a567b

    
           withValues = List.Transform(fields,each _ & " = " & ValueToMCode(Record.Field(value,_))),

Add documentation metadata to ColorContrastRatio.pq

power-query/functions/ColorContrastRatio.pq

Lines 1 to 18 in 543c2e6

    
           (lighter_color as text, darker_color as text) => 
        
           let 
        
               luminosity = (color as text, optional gamma as number) => 
        
                   let 
        
                       gamma_use = if gamma = null then 2.2 else gamma, 
        
                       luminosity =  
        
                           0.2126 * Number.Power(Expression.Evaluate("0x" & Text.Middle(color, 1, 2))/255,gamma_use) + // Red 
        
                           0.7152 * Number.Power(Expression.Evaluate("0x" & Text.Middle(color, 3, 2))/255,gamma_use) + // Green 
        
                           0.0722 * Number.Power(Expression.Evaluate("0x" & Text.Middle(color, 5, 2))/255,gamma_use)  // Blue 
        
                   in 
        
                       luminosity, 
        
               color_contrast =  
        
                   if luminosity(lighter_color)>=luminosity(darker_color) then 
        
                       (luminosity(lighter_color)+0.05)/(luminosity(darker_color)+0.05) 
        
                   else 
        
                       (luminosity(darker_color)+0.05)/(luminosity(lighter_color)+0.05) 
        
           in 
        
               color_contrast

Set up indentation for ValueToMCode

Remove call to "Bandaid" in the bandaid reporting functions

power-query/functions/Bandaid.pq

Line 47 in b8af1b3

    
           #"Added Custom" = try Bandaid[combine](#"Converted to Table"[Value])[bandaid] otherwise null,

This should just be a call to the other field values.

Add metadata documentation to TableCleanFieldNames.pq

See the template in the functions folder.

power-query/functions/TableCleanFieldNames.pq

Lines 1 to 35 in b62a90b

    
           (dirtyTable as table) as table => 
        
           let  
        
               RenameColumn = (ColumnName as text) as text => 
        
                   let  
        
                       SplitColumnName =  
        
                           Text.Combine( 
        
                               List.Transform( 
        
                                   Text.Split( 
        
                                       Text.Replace( 
        
                                           Text.Combine( 
        
                                               Splitter.SplitTextByCharacterTransition({"a".."z"},{"A".."Z"})(ColumnName), 
        
                                               " " 
        
                                           ), 
        
                                           "_", 
        
                                           " " 
        
                                       ), 
        
                                       " " 
        
                                   ), 
        
                                   each  
        
                                       if Text.Length(_)<3 then 
        
                                           Text.Upper(_) 
        
                                       else  
        
                                           Text.Proper(_) 
        
                               ), 
        
                               " " 
        
                           ), 
        
                       FixTrailingNo =  
        
                           if Text.End(SplitColumnName,3) = " NO" then  
        
                               Text.Replace(SplitColumnName," NO"," Number") 
        
                           else  
        
                               SplitColumnName 
        
                   in  
        
                       FixTrailingNo 
        
           in  
        
               Table.TransformColumnNames(dirtyTable,RenameColumn)

Files are missing .pq extension

ValueToMCode has unsupported types

ValueToMCode has several unsupported types

Types

Allow GithubRepository to specify a branch

power-query/functions/GithubRepository.pq

Line 26 in b400f16

    
           url = if input = null then "https://api.github.com/repos/alendaris/power-query/contents" else if Type.Is(Value.Type(input),Record.Type) then "https://api.github.com/repos/" & input[owner] & "/" & input[repository] & "/contents" else input,

Luminosity.pq does not have examples displayed

power-query/functions/Luminosity.pq

Lines 13 to 41 in 4397dec

    
           //ExampleResult = func("#FFCC72", 2.2), 
        
           ExampleResult = func("#0000FF", 2.2), 
        
           fntype = type function 
        
               ( 
        
                   color as (type text meta [ 
        
                       Documentation.FieldCaption = "Color", 
        
                       Documentation.FieldDescription = "Color in RGB hexadecimal format (#RRGGBB)", 
        
                       Documentation.SampleValues = {"#FFCC72"} 
        
                   ]), 
        
                   optional gamma as (type number meta [ 
        
                       Documentation.FieldCaption = "Gamma", 
        
                       Documentation.FieldDescription = "Gamma value for the luminosity calculation (default: 2.2)", 
        
                       Documentation.SampleValues = {2.2} 
        
                   ]) 
        
               ) as table meta 
        
                   [ 
        
                       Documentation.Name = "Luminosity", 
        
                       Documentation.LongDescription = "This function calculates a luminosity value for a given hexadecimal color.", 
        
                       Documentation.Examples = 
        
                       { 
        
                           [ 
        
                               Description = "Determine the luminosity of pure blue (#0000FF).", 
        
                               Code = "Luminosity(""#0000FF"")", 
        
                               Result = 0.0722 
        
                           ] 
        
                       } 
        
                   ]

Might be at issue at L28 where the function return is defined as table, but the result is a number.

power-query/functions/Luminosity.pq

Line 28 in 4397dec

) as table meta

Compressed TableToMCode causes errors in PBI Server environments

power-query/functions/GenerateDateTable.pq

Lines 61 to 66 in b9d4daa

    
           TableToMCode =  
        
               let 
        
                   compressedCode = "zVTRatswFH33V1xUCDYYbXkdTaF1Eyg0HaShLyEU1b5OzGQpk+QlIeRf8i35sklynMbOBnucDbZ879HRuefK4mggr0QKAwAIIJyyD47ANBg3iGBwF3A0wfFwPPjU8TA4jWIffC60+Z4nklel0J9JOpUTudY1XVRDEyl+oTJT2SGiIyVLxxN6WHO3mOPjoZV8XfHCGFTUDx62L9IsC7EIoy5QVJz/S2y4MYq9MV6hpkOlpDpnz+oVvhplF7lQfp9ltcC29Hal3aVIUmkjS9KNI0uXlhg3hiay/CgEhs4DOlVM6FyqMpzVi/XncYMucni3rXMVgVmiAOKGBJBrBGIv6IFnnOCKsxRD/+EMD9+j2AH8g5DIAv3bUpMYSNStf2R5L+onA7jxWySEnZ12Rvc6FdQ+1bpfWInnLWEpiFuIXCzV88F9DDchzyPYWeq/En/2Y1b76Uy5mNuh3cMeIiBBITrVxEEm06pEYZgppLBmzoLHywh1st3vQaDZ2uNEZtbeuAN8RJ2qYnWisfCmcRoYwG1qJ915itsvfgxgpM1orwNkDmNwcdtIZmBd2JamCpk5BbzbUAjXZ/hZodoCZoWRil4reZZi8f+oSeyshbSIQePhNeZVVipFj7hOvqHSTRl9+pX2ryH3lVlK5RFP5Q+EEfKsZEJ8g/V6TadLfHhKU1lZuDA0lSX8Qedww8oVR21ZdrO2fSdZvu+NxgnqipvT53w/D9wBah2xT/AnSfPTTbcrDN0hG7fjYzQsY4aFdfQMi2Jo7ckoCn4D", 
        
                   func = Expression.Evaluate(Text.FromBinary(Binary.Decompress(Binary.FromText(compressedCode), Compression.Deflate)), #shared) 
        
               in 
        
                   func,

power-query/functions/metadata-documentation.pq.template

Lines 8 to 13 in b9d4daa

    
           TableToMCode =  
        
               let 
        
                   compressedCode = "zVTRatswFH33V1xUCDYYbXkdTaF1Eyg0HaShLyEU1b5OzGQpk+QlIeRf8i35sklynMbOBnucDbZ879HRuefK4mggr0QKAwAIIJyyD47ANBg3iGBwF3A0wfFwPPjU8TA4jWIffC60+Z4nklel0J9JOpUTudY1XVRDEyl+oTJT2SGiIyVLxxN6WHO3mOPjoZV8XfHCGFTUDx62L9IsC7EIoy5QVJz/S2y4MYq9MV6hpkOlpDpnz+oVvhplF7lQfp9ltcC29Hal3aVIUmkjS9KNI0uXlhg3hiay/CgEhs4DOlVM6FyqMpzVi/XncYMucni3rXMVgVmiAOKGBJBrBGIv6IFnnOCKsxRD/+EMD9+j2AH8g5DIAv3bUpMYSNStf2R5L+onA7jxWySEnZ12Rvc6FdQ+1bpfWInnLWEpiFuIXCzV88F9DDchzyPYWeq/En/2Y1b76Uy5mNuh3cMeIiBBITrVxEEm06pEYZgppLBmzoLHywh1st3vQaDZ2uNEZtbeuAN8RJ2qYnWisfCmcRoYwG1qJ915itsvfgxgpM1orwNkDmNwcdtIZmBd2JamCpk5BbzbUAjXZ/hZodoCZoWRil4reZZi8f+oSeyshbSIQePhNeZVVipFj7hOvqHSTRl9+pX2ryH3lVlK5RFP5Q+EEfKsZEJ8g/V6TadLfHhKU1lZuDA0lSX8Qedww8oVR21ZdrO2fSdZvu+NxgnqipvT53w/D9wBah2xT/AnSfPTTbcrDN0hG7fjYzQsY4aFdfQMi2Jo7ckoCn4D", 
        
                   func = Expression.Evaluate(Text.FromBinary(Binary.Decompress(Binary.FromText(compressedCode), Compression.Deflate)), #shared) 
        
               in 
        
                   func,

Add function support to ValuesToMCode

Recreate Time dimension table function

GitRepository.pq needs to have input as an optional value

FunctionToMCode -- Retain Metadata for functions

power-query/functions/ValueToMCode.pq

Line 66 in b4a567b

    
           Reinflate = "Text.FromBinary(Binary.Decompress(Binary.FromText(" & TextToMCode(Deflated) & "),Compression.Deflate))"

Some function designers include metadata with their functions (thinking of ImkeF -- and I need to do that more so that my own functions have proper documentation). ValueToMCode only accesses a Metadata called Source Code (which I explicitly set in my external function loader code), so any additional metadata on the parameters or the function itself is lost when the source code is extracted.

Move indent parameter in ValueToMCode into the options record

Want to move the indentation parameter in ValueToMCode into the options record to remove it from the documentation screen for the function. This should not break any other code since it really is an internal option for recursive calls to the function.

	// Runs an Assertion Check on an entire table and returns only the rows where there is an issue
	AssertTable = (inputTable as table, functionToApply as function, optional columnsToInclude as list) =>
	let
	indexedTable = Table.AddIndexColumn(inputTable, "Index", 2, 1, Int64.Type),
	selectedColumns = if (columnsToInclude = null) then Table.ReorderColumns(indexedTable, {"Index"} & Table.ColumnNames(inputTable)) else Table.SelectColumns(indexedTable, {"Index"} & columnsToInclude),
	transformedTable = Table.AddColumn(selectedColumns, "_Assert", functionToApply),
	filteredTable = Table.SelectRowsWithErrors(transformedTable)
	in
	filteredTable,

	(color as text, optional gamma as number) =>
	let
	gamma_use = if gamma = null then 2.2 else gamma,
	luminosity =
	0.2126 * Number.Power(Expression.Evaluate("0x" & Text.Middle(color, 1, 2))/255,gamma_use) + // Red
	0.7152 * Number.Power(Expression.Evaluate("0x" & Text.Middle(color, 3, 2))/255,gamma_use) + // Green
	0.0722 * Number.Power(Expression.Evaluate("0x" & Text.Middle(color, 5, 2))/255,gamma_use), // Blue
	in
	luminosity

	(LibraryName as any, functionName as text) =>
	let
	library =
	if Value.Type(LibraryName) = Text.Type then
	Expression.Evaluate(Expression.Identifier(LibraryName), #shared)
	else
	LibraryName,
	func = Record.Field(library,functionName),
	code = "// " & functionName & "#(cr)#(lf)#(cr)#(lf)" & Value.Metadata(Value.Type(func))[Source Code]
	in
	code

	(uncompressedText as text) as text =>
	let
	Source = Text.From(Binary.Compress(Text.ToBinary(uncompressedText),Compression.Deflate))
	in
	Source

	(background_color as text, optional dark_text as text, optional light_text as text, optional gamma as number) =>
	let
	dark_text_use = if dark_text = null then "#000000" else dark_text,
	light_text_use = if light_text = null then "#FFFFFF" else light_text,
	gamma_use = if gamma = null then 2.2 else gamma,
	luminosity = (color as text, optional gamma as number) =>
	let
	gamma_use = if gamma = null then 2.2 else gamma,
	luminosity =
	0.2126 * Number.Power(Expression.Evaluate("0x" & Text.Middle(color, 1, 2))/255,gamma_use) + // Red
	0.7152 * Number.Power(Expression.Evaluate("0x" & Text.Middle(color, 3, 2))/255,gamma_use) + // Green
	0.0722 * Number.Power(Expression.Evaluate("0x" & Text.Middle(color, 5, 2))/255,gamma_use) // Blue
	in
	luminosity,
	font_color = if luminosity(background_color, gamma_use)>0.5 then dark_text_use else light_text_use
	in
	font_color

	// Unzip
	// Pulled from: https://community.powerbi.com/t5/Power-Query/How-to-connect-Azure-DevOps-REST-API-in-to-power-bi/m-p/895318/highlight/true#M30599
	// Microsoft Power BI Community Forums Member artemus :: https://community.powerbi.com/t5/user/viewprofilepage/user-id/62590

	(ZIPFile) =>
	let
	ushort = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
	uint = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
	EDOCfn = BinaryFormat.Record([
	ZipContent = BinaryFormat.Binary(Binary.Length(ZIPFile) - 22),
	Magic = uint,
	DiskNum = ushort,
	CDirectoryDiskId = ushort,
	CDirectoryRecordCountOnDisk = ushort,
	CDirectoryRecordCount = ushort,
	SizeOfCentralDirectory = uint,
	CentralDirectoryOffset = uint,
	CommendLength = ushort
	]),
	EDOC = EDOCfn(ZIPFile),
	BeforeCentralDirectory = BinaryFormat.Binary(EDOC[CentralDirectoryOffset]),
	CentralDirectory = BinaryFormat.Length(BinaryFormat.Record(
	[
	ZipContent = BeforeCentralDirectory,
	Items = BinaryFormat.List(BinaryFormat.Record(
	[
	Magic = uint,
	CurrentVersion = ushort,
	MinVersion = ushort,
	Flags = ushort,
	CompressionMethod = ushort,
	FileModificationTime = ushort,
	FileModificationDate = ushort,
	CRC32 = uint,
	BinarySize = uint,
	FileSize = uint,
	FileInfo = BinaryFormat.Choice(
	BinaryFormat.Record(
	[
	Len = ushort,
	FieldsLen = ushort,
	FileCommentLength = ushort,
	Disk = ushort,
	InternalFileAttr = ushort,
	ExternalAttr = uint,
	PosOfFileHeader = uint
	]),
	(fileInfo) => BinaryFormat.Record(
	[
	FileName = BinaryFormat.Text(fileInfo[Len], TextEncoding.Ascii),
	Fields = BinaryFormat.Binary(fileInfo[FieldsLen]),
	FileComment = BinaryFormat.Text(fileInfo[FileCommentLength], TextEncoding.Ascii),
	Disk = BinaryFormat.Transform(BinaryFormat.Null, each fileInfo[Disk]),
	InternalFileAttr = BinaryFormat.Transform(BinaryFormat.Null, each fileInfo[Disk]),
	ExternalAttr = BinaryFormat.Transform(BinaryFormat.Null, each fileInfo[InternalFileAttr]),
	PosOfFileHeader = BinaryFormat.Transform(BinaryFormat.Null, each fileInfo[PosOfFileHeader])
	])
	)
	]),
	EDOC[CDirectoryRecordCount]
	)
	]),
	EDOC[CentralDirectoryOffset] + EDOC[SizeOfCentralDirectory]),
	Contents = List.Transform(
	CentralDirectory(ZIPFile)[Items],
	(cdEntry) =>
	let
	ZipEntry = BinaryFormat.Record(
	[
	PreviousData = BinaryFormat.Binary(cdEntry[FileInfo][PosOfFileHeader]),
	Magic = uint,
	ZipVersion = ushort,
	ZipFlags = ushort,
	CompressionMethod = ushort,
	FileModificationTime = ushort,
	FileModificationDate = ushort,
	CRC32 = uint,
	BinarySize = uint,
	FileSize = uint,
	FileName = BinaryFormat.Choice(
	BinaryFormat.Record(
	[
	Len = ushort,
	FieldsLen = ushort
	]),
	(fileInfo) => BinaryFormat.Record(
	[
	FileName = BinaryFormat.Text(fileInfo[Len], TextEncoding.Ascii),
	Fields = BinaryFormat.Binary(fileInfo[FieldsLen])
	])
	),
	FileContent = BinaryFormat.Transform(
	BinaryFormat.Binary(cdEntry[BinarySize]),
	each Binary.Decompress(_, Compression.Deflate)
	)
	])(ZIPFile)
	in
	[FileName=ZipEntry[FileName][FileName], Content=ZipEntry[FileContent]]
	)
	in
	Contents

	Fail = (value as any) as text => "Type Unrecognized",

	Switch = (Expression as any, Values as list, Results as list, optional Else as any) =>
	try Results{List.PositionOf(Values, Expression)}
	otherwise
	if Else = null then Fail
	else Else,

	ToMCode = (value as any,optional indent as number) as any =>
	let
	ParseFunc = Switch(Value.Type(Source),
	{type null,type text,type number},
	{NullToMCode,TextToMCode,NumberToMCode}),
	Return =
	if Value.Is(value,Table.Type) then TableToMCode(value,indent)
	else if Value.Is(value,Record.Type) then RecordToMCode(value,indent)
	else if Value.Is(value,List.Type) then ListToMCode(value,indent)
	else ParseFunc(Source,indent)
	in
	Return,

	FunctionToMCode = (value as function) as text =>
	if Record.HasFields(Value.Metadata(Value.Type(value)),"Source Code") then
	let
	Source = Value.Metadata(Value.Type(value))[Source Code],
	Deflated = Text.From(Binary.Compress(Text.ToBinary(Source),Compression.Deflate)),
	Reinflate = "Text.FromBinary(Binary.Decompress(Binary.FromText(" & Deflated & "),Compression.Deflate))"
	in
	TextToMCode(Reinflate)
	else
	TextToMCode("/* Function without Source Code Metadata */null"),

	ExampleParameter1 = "#FFCC72",
	ExampleParameter2 = "#000000",
	ExampleResult = func(ExampleParameter1,ExampleParameter2),

	fntype = type function
	(
	lighter_color as (type text meta [
	Documentation.FieldCaption = "Color 1",
	Documentation.FieldDescription = "One of two colors to compare in the contrast ratio calculation.",
	Documentation.SampleValues = {ExampleParameter1}
	]),
	darker_color as (type text meta [
	Documentation.FieldCaption = "Color 2",
	Documentation.FieldDescription = "One of two colors to compare in the contrast ratio calculation.",
	Documentation.SampleValues = {ExampleParameter2}
	])
	) as text meta
	[
	Documentation.Name = "ColorContrastRatio",
	Documentation.LongDescription = "This function calculates the Color Contrast Ratio between the lighter color and the darker color.",
	Documentation.Examples =
	{
	[
	Description = "Determine the color contrast ratio between #FFCC72 and #000000.",
	Code = "ColorContrastRatio(""#FFCC72"", ""#000000"")",
	Result = ExampleResult
	]
	}
	]

	(FolderPath as text) =>
	let
	Source = Folder.Files(FolderPath),
	#"Removed Other Columns" = Table.SelectColumns(Source, {"Content", "Name"}),
	#"Remove .pq" = Table.ReplaceValue(#"Removed Other Columns", ".pq", "", Replacer.ReplaceText, {"Name"}),
	#"Pull Source Code" = Table.AddColumn(#"Remove .pq", "Source Code", each Text.Combine(Lines.FromBinary([Content]), "#(lf)")),
	#"Generate Function" = Table.AddColumn(#"Pull Source Code", "Function from File", each Expression.Evaluate([Source Code], #shared)),
	#"Original Function Type" = Table.AddColumn(#"Generate Function", "Original Function Type", each Value.Type([Function from File])),
	#"Add Source Code" = Table.AddColumn(#"Original Function Type", "Function Type Metadata with Source Code Added", each Record.AddField(Value.Metadata([Original Function Type]), "Source Code", [Source Code])),
	#"Regenerate Function" = Table.AddColumn(#"Add Source Code", "Value", each Value.ReplaceType([Function from File], Value.ReplaceMetadata([Original Function Type], [Function Type Metadata with Source Code Added]))),
	#"Removed Other Columns1" = Table.SelectColumns(#"Regenerate Function", {"Name", "Value"}),
	newrecord = Record.FromTable(#"Removed Other Columns1"),
	Library = if Record.HasFields(newrecord, {"Library Source Code"}) then
	[
	Library = newrecord,
	Source Code = newrecord[Library Source Code](newrecord)
	]
	else
	[
	Library = newrecord
	]
	in
	Library

	(libraryName as any) =>
	let
	SourceCodeNamePrefix = "",
	library =
	if Value.Type(libraryName) = Text.Type then
	Expression.Evaluate(Expression.Identifier(libraryName), #shared)
	else
	libraryName,
	functionSourceCode = library[Library Function Source Code],
	funcs = Table.RemoveColumns(Record.ToTable(library), "Value"),
	codeTable = Table.AddColumn(funcs, "Value", each functionSourceCode(libraryName, [Name])),
	#"Replaced Value" = Table.ReplaceValue(codeTable, each [Name], each SourceCodeNamePrefix & [Name], Replacer.ReplaceText, {"Name"}),
	code = Record.FromTable(#"Replaced Value")
	in
	code

	(compressedText as text) as text =>
	let
	Source = Text.FromBinary(Binary.Decompress(Binary.FromText(compressedText), Compression.Deflate))
	in
	Source

	(lighter_color as text, darker_color as text) =>
	let
	luminosity = (color as text, optional gamma as number) =>
	let
	gamma_use = if gamma = null then 2.2 else gamma,
	luminosity =
	0.2126 * Number.Power(Expression.Evaluate("0x" & Text.Middle(color, 1, 2))/255,gamma_use) + // Red
	0.7152 * Number.Power(Expression.Evaluate("0x" & Text.Middle(color, 3, 2))/255,gamma_use) + // Green
	0.0722 * Number.Power(Expression.Evaluate("0x" & Text.Middle(color, 5, 2))/255,gamma_use) // Blue
	in
	luminosity,
	color_contrast =
	if luminosity(lighter_color)>=luminosity(darker_color) then
	(luminosity(lighter_color)+0.05)/(luminosity(darker_color)+0.05)
	else
	(luminosity(darker_color)+0.05)/(luminosity(lighter_color)+0.05)
	in
	color_contrast

	(dirtyTable as table) as table =>
	let
	RenameColumn = (ColumnName as text) as text =>
	let
	SplitColumnName =
	Text.Combine(
	List.Transform(
	Text.Split(
	Text.Replace(
	Text.Combine(
	Splitter.SplitTextByCharacterTransition({"a".."z"},{"A".."Z"})(ColumnName),
	" "
	),
	"_",
	" "
	),
	" "
	),
	each
	if Text.Length(_)<3 then
	Text.Upper(_)
	else
	Text.Proper(_)
	),
	" "
	),
	FixTrailingNo =
	if Text.End(SplitColumnName,3) = " NO" then
	Text.Replace(SplitColumnName," NO"," Number")
	else
	SplitColumnName
	in
	FixTrailingNo
	in
	Table.TransformColumnNames(dirtyTable,RenameColumn)

	//ExampleResult = func("#FFCC72", 2.2),
	ExampleResult = func("#0000FF", 2.2),

	fntype = type function
	(
	color as (type text meta [
	Documentation.FieldCaption = "Color",
	Documentation.FieldDescription = "Color in RGB hexadecimal format (#RRGGBB)",
	Documentation.SampleValues = {"#FFCC72"}
	]),
	optional gamma as (type number meta [
	Documentation.FieldCaption = "Gamma",
	Documentation.FieldDescription = "Gamma value for the luminosity calculation (default: 2.2)",
	Documentation.SampleValues = {2.2}
	])
	) as table meta
	[
	Documentation.Name = "Luminosity",
	Documentation.LongDescription = "This function calculates a luminosity value for a given hexadecimal color.",
	Documentation.Examples =
	{
	[
	Description = "Determine the luminosity of pure blue (#0000FF).",
	Code = "Luminosity(""#0000FF"")",
	Result = 0.0722
	]
	}
	]

	TableToMCode =
	let
	compressedCode = "zVTRatswFH33V1xUCDYYbXkdTaF1Eyg0HaShLyEU1b5OzGQpk+QlIeRf8i35sklynMbOBnucDbZ879HRuefK4mggr0QKAwAIIJyyD47ANBg3iGBwF3A0wfFwPPjU8TA4jWIffC60+Z4nklel0J9JOpUTudY1XVRDEyl+oTJT2SGiIyVLxxN6WHO3mOPjoZV8XfHCGFTUDx62L9IsC7EIoy5QVJz/S2y4MYq9MV6hpkOlpDpnz+oVvhplF7lQfp9ltcC29Hal3aVIUmkjS9KNI0uXlhg3hiay/CgEhs4DOlVM6FyqMpzVi/XncYMucni3rXMVgVmiAOKGBJBrBGIv6IFnnOCKsxRD/+EMD9+j2AH8g5DIAv3bUpMYSNStf2R5L+onA7jxWySEnZ12Rvc6FdQ+1bpfWInnLWEpiFuIXCzV88F9DDchzyPYWeq/En/2Y1b76Uy5mNuh3cMeIiBBITrVxEEm06pEYZgppLBmzoLHywh1st3vQaDZ2uNEZtbeuAN8RJ2qYnWisfCmcRoYwG1qJ915itsvfgxgpM1orwNkDmNwcdtIZmBd2JamCpk5BbzbUAjXZ/hZodoCZoWRil4reZZi8f+oSeyshbSIQePhNeZVVipFj7hOvqHSTRl9+pX2ryH3lVlK5RFP5Q+EEfKsZEJ8g/V6TadLfHhKU1lZuDA0lSX8Qedww8oVR21ZdrO2fSdZvu+NxgnqipvT53w/D9wBah2xT/AnSfPTTbcrDN0hG7fjYzQsY4aFdfQMi2Jo7ckoCn4D",
	func = Expression.Evaluate(Text.FromBinary(Binary.Decompress(Binary.FromText(compressedCode), Compression.Deflate)), #shared)
	in
	func,

alendaris / power-query Goto Github PK

power-query's People

Contributors

Stargazers

Watchers

power-query's Issues

Recommend Projects

Recommend Topics

Recommend Org