Categories
Power BI What's New

Run DAX in Your Browser: SQL-BI.com has a DAX Web App | What’s New

The announcement: sqlbi.com/introducing-dax-do

Examples on Dax.Guide have a “try it” button to launch the editor

Categories
Power BI Power Query Quick Tips

Importing DateTimes from Multiple Cultures in Power Query

Dates and Datetimes use your current culture converting from text .

TransformColumnTypes using the optional parameter: Culture

If they were all the same culture, like en-GB, you can choose column type Using Locale... which will create a step like this:

= Table.TransformColumnTypes(
    Source,
    {{"DateString", type datetime}},
    "en-GB"
)

Import Using Dynamic cultures Using a Lookup Column

Record.Field( record, key) lets you lookup a record field without hard-coding the name. That value is used by DateTime.FromText( string, culture)

let
    // These datetimes are all the same time, using a different 'culture'
    Source = #table(
        type table[DateString = text, User = text],
        {
            { "2/13/2021 7:01:46 PM", "Bob" },
            { "13/02/2021 19:01:46", "Jen" },
            { "13.02.2021 19:01:46", "Kate" }           
        }
    ),
    Culture = [
        Bob = "en-US",
        Jen = "de-DE",
        Kate = "en-GB"
    ],

    // [User] column is mapped to a 'culture'
    // then [DateString] is converted
    DateTime_FromDynamicCulture = (row as record) as datetime =>
        let
            author = row[User],
            culture = Record.Field( Culture, author),
            result = DateTime.FromText( row[DateString], culture)
        in
            result,

    convertDatetimes = Table.AddColumn(
        Source, "DateTime_DynamicCulture",
        each DateTime_FromDynamicCulture(_),
        type datetime    
    )
in
    convertDatetimes

Testing your culture with Culture.Current

Get-Culture in PowerShell

using Get-Culture

Searching for partial matches of english

See More

Using ConvertTo-Culture and Compare-FormatCulture
Categories
Power Query

Word Wrapping Text in Power Query

How to wrap long lines, *without* splitting words. The input is a very long string, with no newlines:

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed eu laoreet turpis. Curabitur lacinia, risus ut rhoncus mattis, turpis lorem iaculis justo, nec ultrices arcu erat vitae felis. Pellentesque vulputate efficitur scelerisque. Etiam bibendum dignissim mauris

Query

List.Accumulate is an aggregate function. I’m using it to “sum” — to add strings together. If the current line plus the next word is longer than 80 characters, then insert a newline first.

To find the length of the current line, I only want the length after the very last newline. Occurrence.Last returns the last match, else -1 if nothing is found.

let  
    LoremIpsum = "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed eu laoreet turpis. Curabitur lacinia, risus ut rhoncus mattis, turpis lorem iaculis justo, nec ultrices arcu erat vitae felis. Pellentesque vulputate efficitur scelerisque. Etiam bibendum dignissim mauris",

    // calculate length of string *after* the rightmost newline
    Text_LengthAfterNewline = (string as text) as number =>
        let 
            posLastNewline = Text.PositionOf(string, "#(lf)", Occurrence.Last),
            posOffset = if posLastNewline <> -1 then posLastNewline else 0,
            deltaLen = Text.Length(string) - posOffset
        in
            deltaLen,

    // word wraps text
    Text_WordWrap = (string as text, max_width as number) as text =>
        let
            words = Text.Split(string, " "),
            accum_result = List.Accumulate(
                words, "", 
                (state as text, current as text) as text =>
                    let
                        len = Text_LengthAfterNewline(state) + Text.Length(current) + 1,
                        maybeNewline =
                            if len > max_width then "#(lf)" else "",

                        accum_string = Text.Combine({state & maybeNewline, current}, " ")
                    in 
                        accum_string
            )
        in
            accum_result,
    
    wrapped_text = Text_WordWrap(LoremIpsum, 80)
in
    wrapped_text

The final result is 80 characters wide or less

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed eu laoreet turpis.
Curabitur lacinia, risus ut rhoncus mattis, turpis lorem iaculis justo, nec
ultrices arcu erat vitae felis. Pellentesque vulputate efficitur scelerisque.
Etiam bibendum dignissim mauris

Validating lengths of each line

let
    Source = #"wrap lines",
    validate_lengths =
        let 
            lines = Text.Split(Source, "#(lf)"),
            lengths = List.Transform(
                lines,
                each [ String = _, Length = Text.Length(_) ])
        in
            Table.FromRecords(
                lengths,
                type table[String = text, Length = number],
                MissingField.Error )
in
    validate_lengths