Forum Discussion

mnt23's avatar
mnt23
Helper I
4 years ago
Solved

Splitting out data from a single cell into multiple columns

Good morning all,    I have some data that I am importing into Power BI desktop and am using Power Query to transform it into something useful.    The data comes from a Power Query online form, t...
Need Help
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 (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLWNzbUNzIwMlIwsLQyNVQISk3OL0stSk2JyTPWN0JIGRgouObm5FempijF6kQrOQG1GukbGkHlTa2MjRRKUotLoNLOQGkzhLSFlYk5sslAKQOIlKEpyGQvX2eQoCXCNjOQmFJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Daily Update" = _t]),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Daily Update", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Daily Update"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Daily Update", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Date", "Time", "Status"})
in
    #"Split Column by Delimiter1"
mnt23's avatar
mnt23
Helper I
4 years ago

Hi Vijay, 

 

Thanks for your help. Running a test with a blank query that looks like it's going to give me what I need. However, I'm struggling to use the code in my actual data - I'm very inexperienced when it comes to using power query!

 

The current code in the advanced editor is this:

 

let
    Source = SharePoint.Tables("https://test.sharepoint.com/teams/22740/CentralLists", [Implementation=null, ApiVersion=15]),
    #"155216eb-e730-45a7-bc21-06fe6bcdb4fb" = Source{[Id="155216eb-e730-45a7-bc21-06fe6bcdb4fb"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(#"155216eb-e730-45a7-bc21-06fe6bcdb4fb",{{"ID", "ID.1"}})
in
    #"Renamed Columns"

 

 

I'm not sure how to then add in the tranformation you've given me the code for. If I just delete everything then it can't find the column - I'm assuming that the current code tranforms the data to give it the column headings in the first place. 

 

How am I supposed to add in a second transformation?

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

    Use this

     

    let
    Source = SharePoint.Tables("https://test.sharepoint.com/teams/22740/CentralLists", [Implementation=null, ApiVersion=15]),
    #"155216eb-e730-45a7-bc21-06fe6bcdb4fb" = Source{[Id="155216eb-e730-45a7-bc21-06fe6bcdb4fb"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(#"155216eb-e730-45a7-bc21-06fe6bcdb4fb",{{"ID", "ID.1"}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Renamed Columns", {{"Daily Update", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Daily Update"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Daily Update", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Date", "Time", "Status"})
in
    #"Split Column by Delimiter1"