Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

power query handling of text and numbers in same column

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):

Screenshot 2020-11-13 153659.jpg

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 

2 ACCEPTED SOLUTIONS

@Anonymous , you're looking for this?

Screenshot 2020-11-13 152931.png

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"

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

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.

112404.jpg

 

Best Regards,
Community Support Team _ Jing Zhang

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

View solution in original post

7 REPLIES 7
CNENFRNL
Community Champion
Community Champion

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

BeforeBefore          AfterAfter


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anand24
Super User
Super User

Hi @Anonymous ,

 

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

cind.PNG

 

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
Not applicable

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

@Anonymous , you're looking for this?

Screenshot 2020-11-13 152931.png

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"

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous
Not applicable

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
Not applicable

hi @CNENFRNL.

for that, can i safely assume before was

Screenshot 2020-11-13 102709.png

 

and the after result is 
Screenshot 2020-11-13 152931.png

 

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

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.

112404.jpg

 

Best Regards,
Community Support Team _ Jing Zhang

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.