Forum Discussion

cottrera's avatar
cottrera
Post Prodigy
4 years ago
Solved

Split rows into columns by

Hi 

 

I have a long single column with 'job number' and 'phone'data on the row. 

 

Comments
Job-1235
Phone-1494879604
Job-1358
Phone-149484757
Job-8695
Phone-01494988765

 

I require a power query function that splits the 'Jobs' into one column and the 'Phone' into another columns.

 

Here is my expected result

JobPhone
Job-1235Phone-1494879604
Job-1358Phone-149484757
Job-8695Phone-01494988765

 

regards

 

Richard 

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Use this

    let
    Source = Csv.Document(File.Contents("C:\Users\cottrera\OneDrive - xxxxxxxxxx.co.uk\Localz\DATA Sent.csv"),[Delimiter=",", Columns=6, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", type text}, {"Column6", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "A"}, {"Column2", "B"}, {"Column3", "C"}, {"Column4", "D"}, {"Column5", "TO Clean"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column6", "A", "B", "C", "D"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each Text.Contains([TO Clean], "Phone") or Text.Contains([TO Clean], "Job")),
    Custom1 = Table.FromColumns({Table.AlternateRows(#"Filtered Rows",1,1,1)[TO Clean],Table.AlternateRows(#"Filtered Rows",0,1,1)[TO Clean]},{"Job","Phone"})
in
    Custom1

4 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8spP0jU0MjZVitWJVgrIyM9L1TU0sTSxMLc0MzABC4JVGJtaoKswMTc1hyuwMLNENsIApMLSwsLcDCgaCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Comments = _t]),
    Custom1 = Table.FromColumns({Table.AlternateRows(Source,1,1,1)[Comments],Table.AlternateRows(Source,0,1,1)[Comments]},{"Job","Phone"})
in
    Custom1

     

    • Vijay_A_Verma's avatar
      Vijay_A_Verma
      Most Valuable Professional
      4 years ago

      Use this

      let
    Source = Csv.Document(File.Contents("C:\Users\cottrera\OneDrive - xxxxxxxxxx.co.uk\Localz\DATA Sent.csv"),[Delimiter=",", Columns=6, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", type text}, {"Column6", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "A"}, {"Column2", "B"}, {"Column3", "C"}, {"Column4", "D"}, {"Column5", "TO Clean"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column6", "A", "B", "C", "D"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each Text.Contains([TO Clean], "Phone") or Text.Contains([TO Clean], "Job")),
    Custom1 = Table.FromColumns({Table.AlternateRows(#"Filtered Rows",1,1,1)[TO Clean],Table.AlternateRows(#"Filtered Rows",0,1,1)[TO Clean]},{"Job","Phone"})
in
    Custom1
    • cottrera's avatar
      cottrera
      Post Prodigy
      4 years ago

      Hi thank you for your quick reponse . You function works fine on the sample data I provided. How could I could add your code to my existing table in power bi?

      Here is my code from the advanced editor.

       

      let
      Source = Csv.Document(File.Contents("C:\Users\cottrera\OneDrive - xxxxxxxxxx.co.uk\Localz\DATA Sent.csv"),[Delimiter=",", Columns=6, Encoding=65001, QuoteStyle=QuoteStyle.None]),
      #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", type text}, {"Column6", Int64.Type}}),
      #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "A"}, {"Column2", "B"}, {"Column3", "C"}, {"Column4", "D"}, {"Column5", "TO Clean"}}),
      #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column6", "A", "B", "C", "D"}),
      #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each Text.Contains([TO Clean], "Phone") or Text.Contains([TO Clean], "Job"))
      in
      #"Filtered Rows"

       

       

      thank you RIchard