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
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"