Forum Discussion
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:
| Column1 | Column2 | |
| Raw1 | L5 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:
| Column1 | Column2 |
| L5 | 0.00% |
| L8 | 0.00% |
| PT | 2.00% |
| P2 | 2.10% |
| L2 | 2.10% |
| L1 | 2.10% |
| L3 | 2.10% |
| 84/88 | 2.10% |
| 24 | 1.70% |
| 79 | 1.80% |
| 75/85 | 2.10% |
| AL | 1.80% |
| MT | 2.10% |
@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
________________________
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 🙂
6 Replies
- FowmySuper 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 🙂
- Denis_SlavHelper III
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 SourceAs 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 SourceBasic split by delimiter is not appropriate, because it's create for each line in Column 1 the same value in Column2.
- FowmySuper User
@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
________________________
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 🙂
- Greg_DecklerCommunity Champion
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?
- amitchandakSuper User
Denis_Slav , not very clear, refer if this can help
https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
- edhansCommunity Champion
Can you post an image of what your data looks like Denis_Slav ? WHen I paste tht in Excel, it looks like this:
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