Forum Discussion
Helper IVA more robust Table.SplitColumn / Splitter.SplitTextByDelimiter
a probably more efficient way would be this:
nrows=Table.RowCount(yourTab), split=Table.FromColumns(List.Zip(List.Transform({0..nrows-1},each Text.Split(yourTab[country]{_},",")))) in splitPS
If Table.TransformRows had, as Table.TransformColumns has, the ability to manage lists of different lengths, there would have been no need to use the List.zip function to do an intermediate step.
aahhh ... 😀 ok, now I see.
you have a table like this (ONLY ONE COUNTRY LIST PER PartNumber. Is it true?)):
and want a table like this (only first rows are displayed):
all you need is in this line (no adda column, no unpivot, no delete lines,...) :
Table.ExpandListColumn(Table.Group(yourTab, {"PartNumber"}, {{"split", each Text.Split(_[country]{0},",")}}),"split")
PS
or more directly e I tink clearer
Table.ExpandListColumn(Table.TransformColumns(yourTab, {"country", each Text.Split(_,",")}),"country")
Helper IVThank you Anonymous . Unfortuneatly the number of coutnries varies from null to a probable max of 50. The end goal is to turn
"PartNumber1", "USA, MEX, CAN"
into something like this duplicating the data
"PartNumber1", "USA"
"PartNumber1", "MEX"
"PartNumber1", "CAN"
Maybe I am using my Excel user interface brain too much and there is an easier PQ function I could be using.
Hardcoding 50 columns and unpivitoing is what could do - but I was hoping for some code I would not have to worry about periodly checking "oh I wonder do I have more than 50 countires today"
Microsoft EmployeeHere is one way to do it in the query editor. I am assuming you want a list of lists for use in a subsequent step, so you can delete the steps after the ListOfLists step, if so.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsKvErzU1KLTJU0lEKDXbUcXb00/F1jVCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each let col1 = [Column1] in List.Transform(Text.Split([Column2], ","), each {col1, _})),
ListOfLists = #"Added Custom"{0}[Custom],
#"Converted to Table" = Table.FromList(ListOfLists, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
TableVersion = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}})
in
TableVersion
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
- OKgo
Thank you mahoneypat
I am really sorry I not great in advanced editor. And I am failing to transplant (and understand) your kind input. I would like to see the first part before taking a look at the list of lists. I removed the BinaryEncoding.Base64 to get to the end of the santax errors and was hit with a null pointer exception I don't quite understand.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("C:\Test.csv"),Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each let col1 = [Column1] in List.Transform(Text.Split([Column2], ","), each {col1, _}))
inTest CSV file looks like this in notepad
"Column1"
"USA, AUT, DEU, LTU, NLD, ESP, GBR, CAN, LVA"
"AUT, DEU, LTU, NLD, ESP, GBR, CAN, LVA"
"USA, AUT, DEU, LTU, NLD, ESP, GBR, LVA"
"AUT, DEU, LTU, NLD, ESP, GBR, CAN, LVA"
"USA, AUT, DEU, LTU, NLD, ESP, GBR, CAN, LVA"
"USA, AUT, DEU, LTU, NLD, ESP, GBR, LVA"
"AUT, DEU, LTU, NLD, ESP, GBR, CAN, LVA"
"AUT, DEU, LTU, NLD, ESP, GBR, CAN, LVA
"AUT, DEU, LTU, NLD, ESP, GBR, CAN, LVA
"USA, ESP, CAN"
"USA, ESP, CAN"Many thanks
- mahoneypat
If you paste the M code I provided into a blank query in the Advanced Editor (replace the text there). Once you do that you can click through each step on the right to see what the data look like at each step (you don't need to delete anything). The Source step is just the example data.
The key to the whole query is the step with "each let col1 = [Column1] ..."
How are you going to use the output of this query? Do you need a list of lists? just a list, a table of values? I'm assuming you need a list of lists to change column names, but please clarify.
Regards,
Pat
Hi OKgo , is not yet completely clear the startong and the ending point, but let me show you what I suppose and what I think can be done.
You have a column like this (which I called "country")
and you want to get a group of columns like these:
This can be done, by this code (with little adaptation, eventually):
yourTab= Table.TransformColumnTypes(Tabella1_Table,{{"country", type text}}), nrows=Table.RowCount(yourTab), colsMax=List.Max(List.Transform({0..nrows-1},each List.Count(Text.PositionOf(yourTab[country]{_},",",Occurrence.All))))+1, countries=List.Transform({1..colsMax},each "col_"&Text.From(_)), colSpl = Table.SplitColumn(yourTab, "country", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), countries) in colSplThe code can manage a variable number of max countries, and create as many columns as needed.
if you still want something different, give examples of the most general case that you want to be treated by giving the input and desired output.
a probably more efficient way would be this:
nrows=Table.RowCount(yourTab), split=Table.FromColumns(List.Zip(List.Transform({0..nrows-1},each Text.Split(yourTab[country]{_},",")))) in splitPS
If Table.TransformRows had, as Table.TransformColumns has, the ability to manage lists of different lengths, there would have been no need to use the List.zip function to do an intermediate step.