Forum Discussion
1 vertical column to several horizontal columns
Hello everyone,
I want to extract the data contained in 1 single column to several columns (See linked file)
The values are spaced in the same way in column A and have the same logic
Thank you in advance for your help.
Best regards
let split = (tbl) => List.Transform(List.Split(List.Skip(tbl[Column1]), 9), (w) => {Table.FirstValue(tbl)} & w), Source = Table.SelectRows(Table.Skip(Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 3), each [Column1] <> null), group = Table.Group(Source, "Column1", {"x", split}, GroupKind.Local, (s, c) => Number.From(Text.Contains(Text.From(c), " : "))), result = Table.FromList(List.Combine(group[x]), (x) => x) in result
6 Replies
The values are spaced in the same way in column A and have the same logicThat does not seem to be the case.
What is the spacing supposed to be ? 16? 13?
Copy and past the folowing code into the advance editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvVzV7BScHRyVorViVZCEG6efmDasag4NS8xB03WCExagUlDhLBrWWpRSX4emG0MJk3BpDmmuc4ZqTnFqYlo5hojmWuAEPZJzUxOLS5JLUJSBDHTBIuLi4FOUAjLzMlBN90Au+lupTkZiblI/rIAk2YIFZ5BPsBACgqJwhFI7ok55YmVaJKG2G1zKcpPz0hNSUSyAx5IsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t]), #"Filtered Rows" = Table.SelectRows(Source, each ([Column] <> "")), #"Grouped Rows" = Table.Group(#"Filtered Rows", "Column", {"Count", each Table.Transpose(_)},0,(x,b)=>Number.From(b="FIN")), #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Column10", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}, {"Column10", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}), #"Added Custom" = Table.AddColumn(#"Expanded Count", "New", each if [Column]="FIN" then [Column10] else [Column]), #"Filled Down" = Table.FillDown(#"Added Custom",{"New"}), Custom1 = #"Filled Down", #"Removed Columns" = Table.Skip(Table.RemoveColumns(Custom1,{"Column", "Column10"})), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"New", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}) in #"Reordered Columns"If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos.
Thank you!Hello lbendlin , Hello Omid_Motamedise
Thank you very much for your message,Ibendlin, you're right to point that out,
I misspoke, perhaps the new file with additional annotations will be more understandable.
Otherwise, I saw that Omid_Motamedise's code removes empty rows which is a good idea for interval problems.Omid_Motamedise, I've tested your code and it works fine with one detail : the league name is not done correctly.
In fact, there's no "FIN" between each league change, hence the problem
Otherwise, it seems to me that the ‘Custom1’ step in your code is unnecessary (same that #Filled Down")I haven't managed to adapt the code to obtain the desired result (see new file).
Thanks for your help
Best regards
OK, I do not access to my computer, I will check and reply to you on Monday.
let split = (tbl) => List.Transform(List.Split(List.Skip(tbl[Column1]), 9), (w) => {Table.FirstValue(tbl)} & w), Source = Table.SelectRows(Table.Skip(Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 3), each [Column1] <> null), group = Table.Group(Source, "Column1", {"x", split}, GroupKind.Local, (s, c) => Number.From(Text.Contains(Text.From(c), " : "))), result = Table.FromList(List.Combine(group[x]), (x) => x) in resultHello AlienSx ,
Perfect, it works very well with so few lines.
Thank you very much, this thread seems to be resolvedOmid_Motamedise , thanks again for your help and your reply,
Have a good weekend
Best regards
