Forum Discussion

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 10, 2024

RecycleBin_Rob 

Updated a bit

as

 

let
    Source = Excel.CurrentWorkbook(){[Name="source"]}[Content],
    quadrants = {" NW", " NE", " SW", " SE", " N", " E", " S", " W"},

    fn = (str as text, add as text) =>
        [
            position = List.Max(
                List.Transform(
                    quadrants,
                    (q) => [ p=Text.PositionOf(
                        str,
                        q & add,
                        Occurrence.Last,
                        Comparer.OrdinalIgnoreCase
                    ),
                    e = if p < 0 then p else p + Text.Length(q)
                    ][e]
                )
            ),
            length =    if      position < 0
                        then    Text.Length(str)
                        else    position
    ][length],


    GetAddress = Table.AddColumn(
        Source,
        "Address",
        each [
            sepMid  = fn([Text], " "),
            sep     = fn([Text], "" ),
            sepEnd  = List.Min({ sepMid, sep }),
            address = Text.Start( [Text], sepEnd)
        ][address]
    )
in
    GetAddress

 

RecycleBin_Rob's avatar
RecycleBin_Rob
Copper Contributor
Oct 11, 2024
Thanks so much for making the adjustments! Next time I will know to provide more dummy data to help illustrate my cases. I'm still developing my Power BI skills so I'm hoping you can offer some more guidance to a rookie when using this formula.

The Excel file isn't actually the source data and allowed me an easier way to see the data as of right now.
Source: Oracle
Navigation: SYNERGY{[Name="WORK_ORDER"]}
Column (with address): [WORK_DESC]

I am not strong at querying and am mistyping something that is leading to errors:
Source = Oracle.Database("Oracle",SYNERGY{[Name="WORK_ORDER"]}[WORK_DESC]),

Any further assistance is greatly appreciated, and if you need more information, please let me know.
    • RecycleBin_Rob's avatar
      RecycleBin_Rob
      Copper Contributor
      Oct 11, 2024

      SergeiBaklan 

       

      Hi, Thanks, I'll set up the Oracle at a later date. Ideally, I can get the Excel working first. I'm still running into a few issues.

       

      I added a new Custom Column (Address) and inserted the coding you provided to my Excel sheet. 

       

       

      When I click on expand (in the column header), I get an error.

       

      When I click on table and try to expand the address, I get an error.

       

      Anymore insight you can provide? Thanks!

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Oct 11, 2024

        RecycleBin_Rob 

        Let assume you have have couple of tables received from Oracle, I imitate them as

        Related queries are

        Let put function which calculates address length into separate query fnAddressLength

        (str as text, add as text) =>

let        
    quadrants = {" NW", " NE", " SW", " SE", " N", " E", " S", " W"},
    position = List.Max(
        List.Transform(
            quadrants,
            (q) => [
                p = Text.PositionOf(
                    str,
                    q & add,
                    Occurrence.Last,
                    Comparer.OrdinalIgnoreCase
                    ),
                e = 
                    if   p < 0
                    then p
                    else p + Text.Length(q)
            ][e]
        )
    ),
    length =
        if   position < 0
        then    Text.Length(str)
        else    position

in
    length

        Finally we merge first two tables from Oracle and from the field Description extract Address

        let
    Source = Table.NestedJoin(
        SV_WORK_ORDER, {"Customer ID"},
        Customer, {"Customer ID"},
        "Customer",
        JoinKind.LeftOuter),
    ExpandCustomer = Table.ExpandTableColumn(
        Source,
        "Customer",
        {"Description"}, {"Description"}),
    KeepColumns = Table.SelectColumns(
        ExpandCustomer,
        {"Work Order ID", "Description"}),

    GetAddress = Table.AddColumn(
        KeepColumns,
        "Address",
        each [
            sepMid  = fnAddressLength([Description], " "),
            sep     = fnAddressLength([Description], "" ),
            sepEnd  = List.Min({ sepMid, sep }),
            address = Text.Start( [Description], sepEnd)
        ][address]
    ),

    SelectColumns = Table.SelectColumns(
        GetAddress,
        {"Work Order ID", "Address"})
in
    SelectColumns

        which gives

        Above is not optimized, just to illustrate an idea. All depends on what are your Oracle tables and which result you'd like to have.