Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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% |
Solved! Go to 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
________________________
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@Denis_Slav , not very clear, refer if this can help
https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
@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?
@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 🙂
⭕ 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
________________________
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |