cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sedric1
Helper III
Helper III

Trying to split a cell into separate rows based on a new line of data

I have a table that looks like this that I am importing into power bi :

CM Program100 Level Videos200 Level - ILT300 Level - ILT400 Level - ILT
Medical StudentSystem - 0154LVL 100: Provider Day in the Life

 100: Introduction to System

 100: Reporting Portal

 100: Patient Portal
0LVL 200: Acute Provider Foundations
Mental Health Provider IPSystem - 0156LVL 100: Provider Day in the Life
 100: Introduction to System
 100: Reporting Portal
 100: Patient Portal
0LVL 200: Acute Provider Foundations

In the 200 Level - ILT column, I have data that contains multiple titles in one cell. I am looking to both unpivot this data as well as break the text into its own row so that I can see which course alignts to which program. An example end state would look like this:

 

CM ProgramTypeCourse
Medical Student200 Level-ILTLVL 100: Provider Day in the Life
Medical Student200 Level-ILT 100: Introduction to System
Medical Student200 Level-ILT 100: Reporting Portal
Medical Student200 Level-ILT 100: Patient Portal
Medical Student100 Level VideosSystem - 0154
Medical Student400 Level - ILTLVL 200: Acute Provider Foundations

 

I assume that I have to unpivot to get the groupings together, but could not seem to get the split column by delimeter option to reak that section up. Any suggestions?

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

@sedric1 

 

No witchcraft - the binary source is just the JSON script that is produced to represent a manually-entered table (when you use "Enter Data" on the ribbon) in Power Query (Query Editor).

 

It's probably easier for you to follow these steps on your Excel source table than explain how to edit the M code:

 

On your Excel source table in Power Query...

1) Select your [200 Level - ILT] column

2) Select Split Column from the ribbon, and choose By Delimiter

3) Select --Custom-- from the delimiter dropdown

4) At the bottom of the dialog, expand Advanced options and check Split using special characters

5) From the Insert special character dropdown, select Line Feed, hit OK

6) Select your CM Program column and Unpivot Other Columns from the ribbon.

 

Voila!

 

Pete

 

View solution in original post

6 REPLIES 6
BA_Pete
Super User
Super User

@sedric1 

 

No witchcraft - the binary source is just the JSON script that is produced to represent a manually-entered table (when you use "Enter Data" on the ribbon) in Power Query (Query Editor).

 

It's probably easier for you to follow these steps on your Excel source table than explain how to edit the M code:

 

On your Excel source table in Power Query...

1) Select your [200 Level - ILT] column

2) Select Split Column from the ribbon, and choose By Delimiter

3) Select --Custom-- from the delimiter dropdown

4) At the bottom of the dialog, expand Advanced options and check Split using special characters

5) From the Insert special character dropdown, select Line Feed, hit OK

6) Select your CM Program column and Unpivot Other Columns from the ribbon.

 

Voila!

 

Pete

 

Thanks a ton! ill mark as answered.

negi007
Super User
Super User

@sedric1 is this the output you are looking for

 

negi007_0-1600875725370.png

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZBBCsIwEEWvMmRdIRV14U4QsdBCsOCmdBGa0QZqIulU6G08iyczVaFgUepCCIQM75P5L8tYgkoXsoKUGoWGWMDStiY8wQR4OJ/5d7yPIeR8CcLZi1boYC1b0AaoRIj1AT3DX9y041ZFQ9jTG9sYJUlbU7M8yJhH+9MPbtfHJ5EhZ1VTdDyQhec2A/pTfIdn60ibIwh/y2p0UPgNff9hLPFT72eLsqKybxWJN1WLv6n6zcxYEV9753c=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"CM Program" = _t, #"100 Level Videos" = _t, #"200 Level - ILT" = _t, #"300 Level - ILT" = _t, #"400 Level - ILT" = _t]),
#"Filtered Rows" = Table.SelectRows(Source, each [#"200 Level - ILT"] <> null and [#"200 Level - ILT"] <> ""),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Filtered Rows", {"100 Level Videos", "200 Level - ILT", "300 Level - ILT", "400 Level - ILT"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Only Selected Columns",{{"Attribute", "Type"}, {"Value", "Course"}}),
#"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each [CM Program] <> null and [CM Program] <> "")
in
#"Filtered Rows1"




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos


Proud to be a Super User!

Follow me here


@negi007 Yes it is! The only issue - which is similar to what Pete did , is that I dont quite know how to implement that solution with my source. It appears you did some step to convert the source file to binary. Perhaps if I can understand what you did there, I can apply the rest to my power bi as well. Thanks!

BA_Pete
Super User
Super User

Hi @sedric1 ,

 

Please try the following code in Power Query:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zY/BCsIwDIZfJfQ8oRP14E0QcTChOPAydyhrdIWZSpcKe3s7tjHwCTyF/H+SL39ZigsaW+sWCg4GiUUiir5jfMEKZLrdxD6/5ZBKuQfl3cca9HDUPVgCbhBy+8A7jX5G7J0JNVsXTQfjodm94tt5tvQEFatuZ11pthE8qZEnJ+Z6cA91YFzIJxfI6AHQiSoZvqe4BGfULTfLWKZ+cuz+OUf1BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"CM Program" = _t, #"100 Level Videos" = _t, #"200 Level - ILT" = _t, #"300 Level - ILT" = _t, #"400 Level - ILT" = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "200 Level - ILT", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"200 Level - ILT.1", "200 Level - ILT.2", "200 Level - ILT.3", "200 Level - ILT.4"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"CM Program"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

 

 

In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this.

 

I get the following output:

BA_Pete_0-1600875406696.png

 

Pete

@BA_Pete  That seems to work! I guess the only question i would have is how to take your source and convert it to my full excel file?

 

I see this line but not quite sure how you got that from the data i pasted, as maybe I can also implement that step with the full file?

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zY/BCsIwDIZfJfQ8oRP14E0QcTChOPAydyhrdIWZSpcKe3s7tjHwCTyF/H+SL39ZigsaW+sWCg4GiUUiir5jfMEKZLrdxD6/5ZBKuQfl3cca9HDUPVgCbhBy+8A7jX5G7J0JNVsXTQfjodm94tt5tvQEFatuZ11pthE8qZEnJ+Z6cA91YFzIJxfI6AHQiSoZvqe4BGfULTfLWKZ+cuz+OUf1BQ==", BinaryEncoding.Base64), Compression.Deflate)),

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!