Forum Discussion

Denis_Slav's avatar
Denis_Slav
Helper III
6 years ago
Solved

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%
  • Fowmy's avatar
    Fowmy
    6 years ago

    @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

6 Replies

    • Denis_Slav's avatar
      Denis_Slav
      Helper 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
          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.

      • Fowmy's avatar
        Fowmy
        Super 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

        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

  • Greg_Deckler's avatar
    Greg_Deckler
    Community 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?

  • edhans's avatar
    edhans
    Community 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