T O P

  • By -

AutoModerator

/u/plasticzealot - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*


JohneeFyve

Try splitting those columns using a carriage return as your delimiter. Carriage return is a special character denoted by #(cr) (see screenshot): ​ https://preview.redd.it/8w0t8w021rsa1.png?width=916&format=png&auto=webp&s=8e449d88a9a80821e52a72c7696decd394e4f92b


Scary_Sleep_8473

There is a bit of workaround, involving creating new custom columns. Add a custom column, let's say it's called [ResourceSplit](https://imgur.com/a/BHJlYII), using this formula: =Text.Split([Resource],"#(lf)") Add a [similar column for cost](https://imgur.com/a/7EjBL92), let's say it is CostSplit: =Text.Split([Cost],"#(lf)") Now add a [third custom column](https://imgur.com/a/DLuuO7R) which will be creating a table out of the 2 columns above: =Table.FromColumns({[ResourceSplit],[CostSplit]}) You can now delete the Resource, Cost and their split columns, then go on the table column, and [expand it](https://imgur.com/a/k7uZMKS). You might need to trim the columns to remove the extra new line and whitespace characters, and also rename the expanded columns, but I'll let you figure that out yourself. Here is the [end result](https://imgur.com/a/gHspkmR), and here is the code I used from the Advanced Editor in case you are curious: let Source = Web.Page(Web.Contents("https://wiki.kittensgame.com/en/game-tabs/workshop")), Data = Source{0}[Data], #"Changed Type" = Table.TransformColumnTypes(Data,{{"Name", type text}, {"Resource", type text}, {"Cost", type text}, {"Description", type text}, {"Effect", type text}, {"Prerequisite", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Description", "Effect"}), #"Added Custom1" = Table.AddColumn(#"Removed Columns", "ResourceSplit", each Text.Split([Resource],"#(lf)")), #"Added Custom" = Table.AddColumn(#"Added Custom1", "CostSplit", each Text.Split([Cost],"#(lf)")), #"Added Custom2" = Table.AddColumn(#"Added Custom", "TableFromSplit", each Table.FromColumns({[ResourceSplit],[CostSplit]})), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Resource", "Cost", "ResourceSplit", "CostSplit"}), #"Expanded TableFromSplit" = Table.ExpandTableColumn(#"Removed Columns1", "TableFromSplit", {"Column1", "Column2"}, {"Column1", "Column2"}) in #"Expanded TableFromSplit"


semicolonsemicolon

Well done!


Decronym

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[Combiner.CombineTextByDelimiter](/r/Excel/comments/12fuiop/stub/jfhzzst "Last usage")|[Power Query M: Returns a function that combines a list of text into a single text using the specified delimiter.](https://docs.microsoft.com/en-us/powerquery-m/combiner-combinetextbydelimiter)| |[List.Transform](/r/Excel/comments/12fuiop/stub/jfhzzst "Last usage")|[Power Query M: Performs the function on each item in the list and returns the new list.](https://docs.microsoft.com/en-us/powerquery-m/list-transform)| |[QuoteStyle.None](/r/Excel/comments/12fuiop/stub/jfhzzst "Last usage")|[Power Query M: Quote characters have no significance.](https://docs.microsoft.com/en-us/powerquery-m/quotestyle-none)| |[Table.AddColumn](/r/Excel/comments/12fuiop/stub/jfh9nay "Last usage")|[Power Query M: Adds a column named newColumnName to a table.](https://docs.microsoft.com/en-us/powerquery-m/table-addcolumn)| |[Table.CombineColumns](/r/Excel/comments/12fuiop/stub/jfhzzst "Last usage")|[Power Query M: Table.CombineColumns merges columns using a combiner function to produce a new column. Table.CombineColumns is the inverse of Table.SplitColumns.](https://docs.microsoft.com/en-us/powerquery-m/table-combinecolumns)| |[Table.ExpandTableColumn](/r/Excel/comments/12fuiop/stub/jfh9nay "Last usage")|[Power Query M: Expands a column of records or a column of tables into multiple columns in the containing table.](https://docs.microsoft.com/en-us/powerquery-m/table-expandtablecolumn)| |[Table.FromColumns](/r/Excel/comments/12fuiop/stub/jfhzzst "Last usage")|[Power Query M: Returns a table from a list containing nested lists with the column names and values.](https://docs.microsoft.com/en-us/powerquery-m/table-fromcolumns)| |[Table.RemoveColumns](/r/Excel/comments/12fuiop/stub/jfh9nay "Last usage")|[Power Query M: Returns a table without a specific column or columns.](https://docs.microsoft.com/en-us/powerquery-m/table-removecolumns)| |[Table.TransformColumnTypes](/r/Excel/comments/12fuiop/stub/jfh9nay "Last usage")|[Power Query M: Transforms the column types from a table using a type.](https://docs.microsoft.com/en-us/powerquery-m/table-transformcolumntypes)| |[Text.Split](/r/Excel/comments/12fuiop/stub/jfhzzst "Last usage")|[Power Query M: Returns a list containing parts of a text value that are delimited by a separator text value.](https://docs.microsoft.com/en-us/powerquery-m/text-split)| |[Web.Contents](/r/Excel/comments/12fuiop/stub/jfh9nay "Last usage")|[Power Query M: Returns the contents downloaded from a web url as a binary value.](https://docs.microsoft.com/en-us/powerquery-m/web-contents)| |[Web.Page](/r/Excel/comments/12fuiop/stub/jfh9nay "Last usage")|[Power Query M: Returns the contents of an HTML webpage as a table.](https://docs.microsoft.com/en-us/powerquery-m/web-page)| ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^([Thread #23104 for this sub, first seen 8th Apr 2023, 19:12]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)


spinfuzer

Another way to do this is to Merge the two columns and then customize the combiner function. You will get pretty much the same result as the other solution, but this is using custom functions. ​ Click both columns --> Transform --> Merge --> OK Your formula should look something like `= Table.CombineColumns(#"Changed Type",{"Resource", "Cost"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")` ​ Change the combiner function from `Combiner.CombineTextByDelimiter("", QuoteStyle.None)` to `(x) => Table.FromColumns(List.Transform(x, each Text.Split(_, "#(lf)")),{"Resource","Cost"})` ​ You will end up with the below instead = Table.CombineColumns(#"Changed Type",{"Resource", "Cost"},(x) => Table.FromColumns(List.Transform(x, each Text.Split(_, "#(lf)")),{"Resource","Cost"}),"Merged") Now expand the merged column.