Forum Discussion

vanessafvg's avatar
vanessafvg
Community Champion
9 years ago
Solved

M Gurus help :)

I have a facebook post that has hundres of entries in it that i want to pull into different rows and columns.   I think mostly what i am struggling with is, a removing the first 2 lines that are highlighted, then splitting the date and % into 2 columns and different rows for each line.   Any tips would be great.

 

Need Help
  • erik_tarnvik's avatar
    erik_tarnvik
    9 years ago

    Remove top three lines (or four, hard to tell from picture) and then split column delimited by "- ". That would generate the M code below.

     

    let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\testdata.txt"), null, null, 1252)}),
    #"Removed Top Rows" = Table.Skip(Source,3),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Top Rows", "Column1", Splitter.SplitTextByDelimiter("- ", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", Percentage.Type}})
in
    #"Changed Type"

     

3 Replies

  • erik_tarnvik's avatar
    erik_tarnvik
    Solution Specialist
    9 years ago

    Remove top three lines (or four, hard to tell from picture) and then split column delimited by "- ". That would generate the M code below.

     

    let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\testdata.txt"), null, null, 1252)}),
    #"Removed Top Rows" = Table.Skip(Source,3),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Top Rows", "Column1", Splitter.SplitTextByDelimiter("- ", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", Percentage.Type}})
in
    #"Changed Type"

     

    • erik_tarnvik's avatar
      erik_tarnvik
      Solution Specialist
      9 years ago

      If you dont want the first column but only the percentages you can delete it in the query editor of course.