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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Denis_Slav
Helper III
Helper III

Split lines in rows to table M Query

Hello,

How I can split in each raw each line in record to several raws in M Query? The raw data looks like that:

 Column1Column2
Raw1L5
L8
PT
P2
L2
L1
L3
84/88
24
79
75/85
AL
MT
0.00%
0.00%
2.00%
2.10%
2.10%
2.10%
2.10%
2.10%
1.70%
1.80%
2.10%
1.80%
2.10%


And I like to get next:

Column1Column2
L50.00%
L80.00%
PT2.00%
P22.10%
L22.10%
L12.10%
L32.10%
84/882.10%
241.70%
791.80%
75/852.10%
AL1.80%
MT2.10%
1 ACCEPTED SOLUTION

@Denis_Slav

Duplicate the source and divide by delimiter using the line font into rows and merge the two queries.

You can download the file: HERE

Fowmy_0-1596986071783.png

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click the Thumbs-Up icon on the right if you like this answer 🙂

YoutubeLinkedin

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

6 REPLIES 6
edhans
Super User
Super User

Can you post an image of what your data looks like @Denis_Slav ? WHen I paste tht in Excel, it looks like this:

edhans_0-1596926140574.png

Those are all on separate rows, and columns, so nothing to split.

If you can provide a link to your actual source file via OneDrive, Dropbox, etc. that would help as I suspect you have some weird CR/LF characters that are getting filtered out when you paste here.

 

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
amitchandak
Super User
Super User

Greg_Deckler
Super User
Super User

@Denis_Slav - So are you saying that if this data was in Excel, the word Raw would be in Cell A1 and then all of Column1 would be in cell B1 and all of Column2 would be in cell C1?

 

Is this a text file or something else?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Fowmy
Super User
Super User

@Denis_Slav 

How does your source data look like?. Both the tables you have shown are same.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@Fowmy @Greg_Deckler @amitchandak @edhans  Yes, it's not so clear then I supose. )) 

 

My source is PDF file, in source it like matrix, and I need to create table like KEY-VALUE. To make more clear I create a sample:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XYo9DoAgDEbv0sTN8I/UkR0TBzbKFXr/0Zq64PDy+vJ1DGiZuCHx3YUg94sXIjEmizKFRFxOIVuU79qIrw47OOPcRvzJm6jKqrTUsZRXBVX5F8z5AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t])
in
    Source

 As you can see, there are one raw with data in several lines with [Line Feed} in end of each line. I need generate separate raw from each line, like this sample:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jFV0lEy0DMwUFWK1QFyLVC4ASFArqGeMYxrBOaawhRDuCZAXiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t])
in
    Source

 Basic split by delimiter is not appropriate, because it's create for each line in Column 1 the same value in Column2.

@Denis_Slav

Duplicate the source and divide by delimiter using the line font into rows and merge the two queries.

You can download the file: HERE

Fowmy_0-1596986071783.png

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click the Thumbs-Up icon on the right if you like this answer 🙂

YoutubeLinkedin

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.