power query handling of text and numbers in same column

[Anonymous]

hi,

as an intro, i'm trying to extract/transform (ms excel) financial data using power query. 

previously, i managed to do for all accounts successfully as i could split between their prefix account code (numeric) and account name (text string). 

however, recently the client has changed its chart of accounts which resulted in certain items (like retained earnings) now not having the prefix account codes whilst still come under the same column (as shown in the image below):

kindly guide me on how to extract the info for retained earnings since it no longer has the account codes (using power query) & that we now have both number & text string data in the same column.

tks & krgds, -nik 

[CNENFRNL · Accepted solution]

@Anonymous , you're looking for this?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0MDBQcExOzi/NKylWCEpNTs0sS0zKSS1WitUBShuhSAckViLkglJLEjPzUlMUXBOL8jLz0iGi/iUZqUVAZiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Extract = Table.TransformColumns(Source,
        {{"Column1", each
            [
                substr = try Text.Start(_, Text.PositionOf(_, " ", 0)) otherwise "",
                num = Text.Select(substr, {"0".."9"}),
                split = [code = num, str = Text.Trim(Text.Replace(_, num, ""))]
            ][split]
        }}
    ),
    #"Expanded Column1" = Table.ExpandRecordColumn(Extract, "Column1", {"code", "str"}, {"code", "category"})
in
    #"Expanded Column1"

[CNENFRNL]

Hi, @Anonymous , you might want to try such a solution

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0MDBQcExOzi/NKylWCEpNTs0sS0zKSS1WitUBShuhSAckViLkglJLEjPzUlMUXBOL8jLz0iGi/iUZqUVAZiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Extract = Table.TransformColumns(Source,
        {{"Column1", each
            [
                substr = try Text.Start(_, Text.PositionOf(_, " ", 0)) otherwise "",
                num = Text.Select(substr, {"0".."9"}),
                str = Text.Trim(Text.Replace(_, num, ""))
            ][str]
        }}
    )
in
    Extract

Before          After

[Anand24]

Hi @Anonymous ,

You can use a conditional column to get details of Retained Earnings.

For getting codes, you can simply split your original column by first space to left.

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

[Anonymous]

tks, @Anand24 & @CNENFRNL.

many thanks @Anand24 & @CNENFRNL.

can 'retained earnings' (& probably some other items that don't have the account codes too) be 'shifted' to another column using power query so that i can work on them differently? currently such items without the account codes are in the same column as those with account codes.

krgds, -nik

[CNENFRNL · Accepted solution]

@Anonymous , you're looking for this?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0MDBQcExOzi/NKylWCEpNTs0sS0zKSS1WitUBShuhSAckViLkglJLEjPzUlMUXBOL8jLz0iGi/iUZqUVAZiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Extract = Table.TransformColumns(Source,
        {{"Column1", each
            [
                substr = try Text.Start(_, Text.PositionOf(_, " ", 0)) otherwise "",
                num = Text.Select(substr, {"0".."9"}),
                split = [code = num, str = Text.Trim(Text.Replace(_, num, ""))]
            ][split]
        }}
    ),
    #"Expanded Column1" = Table.ExpandRecordColumn(Extract, "Column1", {"code", "str"}, {"code", "category"})
in
    #"Expanded Column1"

[Anonymous]

many tks, @v-jingzhang , @CNENFRNL.

i'm also sorry fir thr late reply as i was heavily involved in a project.

kindest regards, -nik

[Anonymous]

hi @CNENFRNL.

for that, can i safely assume before was

and the after result is 

if yes, then that's what i need.

hwvr, as i'm not familiar with m-language/code, pls advise what i need to do with the code.

tks, -nik

[v-jingzhang · Accepted solution]

Hi @Anonymous , has the problem been solved? If so, kindly accept the appropriate post as the solution to help other members find it quickly. Thanks very much.

If not yet, click Advanced Editor in Home tab, copy and paste the corresponding M codes into it. See the image below. Take note that there is a comma between steps and "Expanded Column1" is the name of the last step after applied. You will find it in Applied Steps panel. "Changed Type" is the name of the previous step.

Best Regards,
Community Support Team _ Jing Zhang

If this post helps, please consider Accept it as the solution to help other members find it.