bjulius

The Python code interpreter, combined with various libraries, can handle a wide range of file formats. Here are some of the most common ones:

Text Files: Plain text (.txt), comma-separated values (.csv), tab-separated values (.tsv), and other delimited text files.

Excel Files: Excel Workbooks (.xls, .xlsx) can be read using libraries like pandas and openpyxl.

JSON Files: JavaScript Object Notation (.json) is a common data interchange format.

XML and HTML Files: These can be parsed using libraries like BeautifulSoup and lxml.

bjulius

// C# Tabular Editor Script to Export All DAX Measures to Text File
// Thanks to Pavel Adam for this code

var vDT =  DateTime.UtcNow ;
string sDateTime =  vDT.ToString() ;
string sTargetDir = "C:\\Temp\\";
string sFilename = vDT.ToString("yyyy-mm-dd HH-mm-ss") ;
string newLine = "\r\n";
string measureSeparator = "=========";
string result = "" + measureSeparator + newLine + sDateTime + newLine + measureSeparator + newLine;

bjulius

let
    Source = Table.DemoteHeaders( Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
    Transpose = Table.FillDown( Table.TransformColumns( Table.Transpose(Source), { "Column1", each if Text.StartsWith(_, "G") then _ else null}), {"Column1"}),
    UnpivotOther = Table.UnpivotOtherColumns(Transpose, {"Column1"}, "Attribute", "Value"),
    AddDept = Table.SelectRows( Table.SelectColumns( Table.AddColumn(UnpivotOther, "Dept", each if not List.Contains({"1".."9"}, Text.At(Text.From( [Value]), 0)) then [Value] else null), {"Column1", "Dept"}), each [Dept] <> null),
    AddTeam = Table.TransformColumnTypes( Table.SelectRows( Table.SelectColumns( Table.AddColumn(UnpivotOther, "Team", each if List.Contains({"1".."9"}, Text.At(Text.From( [Value]), 0)) then [Value] else null), {"Column1", "Team"}), each [Team] <> null ), {"Team", Text.Type}),
    Table = Table.FromColumns({
        Table.First( Table.FromColumns( List.Zip({Table.ToColumns(AddDept), Table.ToColumns(AddTeam)})))[Column1],
        Table.First( Table

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddIdx = Table.Buffer(Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type)),
    SplitbyCharTrans = Table.SplitColumn(AddIdx, "Text Time", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Text Time.1", "Text Time.2", "Text Time.3", "Text Time.4"}),
    UnpivotOther = Table.RemoveColumns(Table.UnpivotOtherColumns(SplitbyCharTrans, {"Add", "Index"}, "Attribute", "Value"), {"Attribute", "Add"}),
    SplitbySpace = Table.TransformColumnTypes( Table.SplitColumn(UnpivotOther, "Value", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Value.1", "Value.2"}), {"Value.1", Number.Type}),
    FirstChar = Table.RemoveColumns( Table.AddColumn(SplitbySpace, "Category", each Text.Start(Text.Upper([Value.2]), 1), type text), {"Value.2"}),
    ConvertSecs = Table.AddColumn(FirstChar, "Seconds", each if [Category] = "S" then [Value.1] else
        if [Category] = "M" then [Value.1] * 60 else

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FIlter = Table.SelectRows(Source, each ([Column1] <> null and [Column1] <> "Date")),
    AddDate = Table.TransformColumnTypes(Table.AddColumn(FIlter, "Date", each if Value.Type( [Column1] ) = DateTime.Type then [Column1] else null), {"Date", Date.Type}),
    AddPO = Table.AddColumn(AddDate, "PONumber", each if Value.Type([Column3]) = Text.Type then [Column3] else null),
    AddSupplier = Table.AddColumn(AddPO, "Supplier ", each if [Column2] <> null then [Column2] else null),
    AddTax = Table.AddColumn(AddSupplier, "Tax", each if Text.StartsWith( Text.From( [Column1]), "Tax") then [Column3] else null),
    AddAmount = Table.RemoveColumns( Table.AddColumn(AddTax, "Amount", each if Text.StartsWith( Text.From( [Column1]), "Amount") then [Column3] else null), {"Column1", "Column2", "Column3"}),
    RemBlanks = Table.AddIndexColumn( Table.SelectRows(AddAmount, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))

bjulius

let
    ReplaceBlanksWithNulls = (inputTable as table) as table =>
        let
            columnNames = Table.ColumnNames(inputTable),
            replaceBlanksInColumn = (table, columnName) => Table.ReplaceValue(table, "", null, Replacer.ReplaceValue, {columnName}),
            transformedTable = List.Accumulate(columnNames, inputTable, (state, current) => replaceBlanksInColumn(state, current))
        in
            transformedTable
in
    ReplaceBlanksWithNulls

bjulius

let
    Source = Table.TransformColumnTypes( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], {"Date Time", type datetime}),
    Sort = Table.Sort(Source,{{"Emp ID", Order.Ascending}, {"Date Time", Order.Ascending}}),
    Group = Table.Group(Sort, {"Emp ID"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Emp ID=nullable number, Date Time=nullable datetime, Category=nullable text]}, {"MaxDateTime", each List.Max([Date Time]), type nullable datetime}}),
    AddIndex = Table.RemoveColumns( Table.AddColumn(Group, "Index", each Table.AddIndexColumn([All], "Idx", 0, 1)), "All"),
    AddModulo = Table.RemoveColumns( Table.AddColumn(AddIndex, "Modulo", each Table.AddColumn( [Index], "Mod2", each Number.Mod([Idx], 2))), "Index"),
    Expand = Table.ExpandTableColumn(AddModulo, "Modulo", {"Emp ID", "Date Time", "Category", "Idx", "Mod2"}, {"Emp ID.1", "Date Time", "Category", "Idx", "Mod2"}),
    AddProblems = Table.AddColumn(Expand, "Problems", each 
        [
        a = if Number.IsOd

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Headers = Table.ColumnNames( Source ),
    ToRecs = Table.ToRecords( Source ),
    ToTable = Table.FromList(ToRecs, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    AddResultRemain = Table.AddColumn(ToTable, "ResultRemain", each [
        a = Text.Split( List.First( Record.ToList( [Column1] )), ", "),
        b = List.Skip( Record.ToList( [Column1]), 1),
        c = List.Count(a),
        d = List.Count(List.RemoveNulls(b)),

bjulius

Power BI report file

bjulius

CF Autoformat Font = 

// based on Ed Freeman's blog entry// https://endjin.com/blog/2020/06/how-to-dynamically-choose-the-correct-font-colour-based-on-a-background-colour-in-power-bi-tables

// 1. Grab the background colour for the current row (you will need to change the [CF Rank] measure to whatever measure returns your conditional formatting background color hex codes

VAR selectedColour = [CF Rank]

// 2. Extract the hexadecimal value for each digit in each colour couplet, and translate to the decimal representation of that value