bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    UnpivotOther = Table.UnpivotOtherColumns(AddIndex, {"Name", "Date", "Hours", "Index"}, "Attribute", "Value"),
    Group = Table.Group(UnpivotOther, {"Index"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Name=text, Date=datetime, Hours=number, Index=number, Attribute=text, Value=text]}}),
    Expand = Table.ExpandTableColumn(Group, "All", {"Name", "Date", "Hours", "Attribute", "Value"}, {"Name", "Date", "HoursX", "Attribute", "Value"}),
    AddHours = Table.AddColumn(Expand, "Hours", each [HoursX]/[Count]),
    RemoveCols = Table.RemoveColumns(AddHours,{"Index", "Count", "HoursX"}),
    ReType = Table.TransformColumnTypes(RemoveCols,{{"Date", type date}}),
    AddIndex0 = Table.AddIndexColumn(ReType, "Index", 0, 1, Int64.Type),

bjulius

// Creates a MIN, MAX, SUM, AVERAGE and MEDIAN measure for every currently selected column
foreach(var c in Selected.Columns)
{
    var newMeasure = c.Table.AddMeasure(
        "Min of " + c.Name,                    // Name
        "MIN(" + c.DaxObjectFullName + ")",    // DAX expression
        c.DisplayFolder);                    // Display Folder
       
    var newMeasure2 = c.Table.AddMeasure(
        "Max of " + c.Name,                    // Name

bjulius

/*
 * Title: Auto-generate COUNTROWS measures from tables
 * 
 * Author: Edgar Walther, twitter.com/edgarwalther
 * 
 * This script, when executed, will loop through the currently selected tables,
 * creating one COUNTROWS measure for each table.
 */
 
 // Loop through all currently selected tables:

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Retype = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    Group = Table.Group(Retype, {"Store"}, {"All", each [Date]}),
    AddDateLisit = Table.AddColumn(Group, "DateList", each [
        a = List.Min( [All] ),
        b = List.Max ( [All] ),
        c = Number.From( b ) - Number.From( a ) + 1,
        d = List.Dates( a, c, #duration(1,0,0,0))
    ][d]),

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    MorseTable = 
    [
        Source = Table.ToColumns( Excel.CurrentWorkbook(){[Name="Table3"]}[Content]),
        Letters = List.Union( {Source{0}, Source{2}, Source{4}}),
        Code = List.Union({ Source{1}, Source{3}, Source{5}}),
        TabFromCols = Table.FromColumns({Letters, Code}, {"Letters", "Code"}),
        Replace = Table.ReplaceValue(TabFromCols,null," ",Replacer.ReplaceValue,{"Letters"}),
        Replace2 = Table.ReplaceValue(Replace,null,"/",Replacer.ReplaceValue,{"Code"}),

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddToList = Table.RemoveColumns( Table.DuplicateColumn( Table.AddColumn(Source, "StripList", each Text.ToList([LSTRIP Chars])), "Input String", "OriginalString"), "LSTRIP Chars"),
    AddOrigToList = Table.AddColumn(AddToList, "OrigToList", each Text.ToList([OriginalString] )),
    Expand = Table.ExpandListColumn(AddOrigToList, "OrigToList"),
    AddInStriplist = Table.AddColumn(Expand, "InStripList", each if List.Contains( [StripList], [OrigToList]) then "Strip" else "Keep"),
    Remove = Table.SelectColumns(AddInStriplist,{"InStripList", "Input String"}),
    Reorder = Table.ReorderColumns(Remove,{"Input String", "InStripList"}),
    GroupKindLocal = Table.Group(Reorder, {"Input String", "InStripList"}, {{"Remove", each Table.RowCount(_), Int64.Type}}, GroupKind.Local),
    Regroup = Table.Group(GroupKindLocal, {"Input String"}, {{"All", each _, type table [Input String=text, InStripList=text, Remove=number]}}),

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    
    StringTable = [
    StrTable = Table.AddColumn( Table.SelectColumns( Source, "Masked String"), "Chars", each Text.ToList( [#"Masked String"] )),
    AddPositions = Table.AddColumn(StrTable, "Position", each List.Positions([Chars])),
    AddZipPositions = Table.RemoveColumns( Table.AddColumn(AddPositions, "AsteriskPositions", each List.Zip( {[Chars], [Position] })), {"Chars", "Position"}),
    Expand = Table.ExpandListColumn(AddZipPositions, "AsteriskPositions"),
    Extract = Table.TransformColumns(Expand, {"AsteriskPositions", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    Split = Table.SplitColumn(Extract, "AsteriskPositions", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"AsteriskPositions.1", "AsteriskPositions.2"}),

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ExtractDate = Table.TransformColumns(Source,{{"Date", DateTime.Date, type date}}),
    CreateCrossProd = Table.AddColumn(ExtractDate, "Custom", each 
    [
        ProductList = List.Distinct( ExtractDate[Product] ),
        ActionList = List.Distinct( ExtractDate[Action]),
        MinDate = List.Min( ExtractDate[Date] ),
        MaxDate = List.Max( ExtractDate[Date] ),
        Products = {"Fruit", "Vegetable", "Meat"},

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DupeCol = Table.DuplicateColumn(Source, "Animals", "Animals2"),
    Split = Table.ExpandListColumn(Table.TransformColumns(DupeCol, {{"Animals2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Animals2"),
    #"Added Index" = Table.AddIndexColumn(Split, "SortOrder", 1, 1, Int64.Type),
    Trim = Table.TransformColumns(#"Added Index",{{"Animals2", Text.Trim, type text}}),
    Group = Table.Group(Trim, {"Animals", "Animals2"}, {{"All", each _, type table [Animals=text, Animals2=text]}, {"Count", each Table.RowCount(_), Int64.Type}}),
    AddIdx = Table.AddColumn(Group, "AddIdx", each Table.AddIndexColumn( [All], "Index", 1, 1)),
    Expand = Table.ExpandTableColumn(AddIdx, "AddIdx", {"Animals2", "Index", "SortOrder"}, {"Animals2.1", "Index", "SortOrder"}),
    Sort = Table.Sort(Expand,{{"SortOrder", Order.Ascending}}),

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Dupe = Table.DuplicateColumn(Source, "String", "String2"),
    #"SplitBy-" = Table.ExpandListColumn(Table.TransformColumns(Dupe, {{"String2", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "String2"),
    SplitByCharTrans = Table.SplitColumn(#"SplitBy-", "String2", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c))),
    Add1stPosCheck = Table.AddColumn(SplitByCharTrans, "FirstPos", each if Text.StartsWith( [String], [String2.1]) then 0 else 1),
    Filter = Table.SelectRows(Add1stPosCheck, each ([FirstPos] = 1)),
    ReType = Table.TransformColumnTypes(Filter,{{"String2.1", Int64.Type}}),
    #"Multiply-1" = Table.AddColumn(ReType, "Multiplication", each [String2.1] * -1, type number),
    GroupSum = Table.Group(#"Multiply-1", {"String"}, {{"Answer", each List.Sum([Multiplication]), type number}}),