Forum Discussion
Is it possible to Ungruoup one column into 3 columns using get & transfrom
Hi Joana,
Perhaps there is more clever solution, but i did that in straigforward way - split Article on levels and fill each level by it names. Steps are in attached file, generated code is
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], DuplicateArticle = Table.AddColumn(Source, "Artcl", each [Article]), NullValueToZero = Table.ReplaceValue(DuplicateArticle,null,0,Replacer.ReplaceValue,{"Value"}), SplitArticleOnLevels = Table.SplitColumn(NullValueToZero, "Article", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Article.1", "Article.2", "Article.3"}), EmptyToNull = Table.ReplaceValue(SplitArticleOnLevels,"",null,Replacer.ReplaceValue,{"Article.2", "Article.3"}), AddFirstLevel = Table.AddColumn(EmptyToNull, "Custom1", each if [Article.2] = null then [Description] else null), FilledItDown = Table.FillDown(AddFirstLevel,{"Custom1"}), AddSecondLevel = Table.AddColumn(FilledItDown, "Custom2", each if [Article.3] = null and [Article.2] <> null then [Description] else null), NotRequired = Table.AddColumn(AddSecondLevel, "Custom22", each if [Article.2] = null then "" else [Custom2]), FillSecond = Table.FillDown(NotRequired,{"Custom22"}), RemoveNotRequired = Table.RemoveColumns(FillSecond,{"Custom2"}), AddThirdLevel = Table.AddColumn(RemoveNotRequired, "Custom3", each if [Article.2] <> null and [Article.3] <> null then [Description] else ""), RenameColumns = Table.RenameColumns(AddThirdLevel,{{"Artcl", "Article"}, {"Custom1", "Group"}, {"Custom22", "sub-group"}}), RemoveTempColumns = Table.SelectColumns(RenameColumns,{"Value", "Article", "Group", "sub-group", "Custom3"}), RenameFinally = Table.RenameColumns(RemoveTempColumns,{{"Custom3", "description"}}), Reordered = Table.ReorderColumns(RenameFinally,{"Article", "Group", "sub-group", "description", "Value"}), ProperType = Table.TransformColumnTypes(Reordered,{{"Article", type text}, {"Group", type text}, {"sub-group", type text}, {"description", type text}, {"Value", type number}}) in ProperType
Hi Joana,
I assume your source data is in the table (you may use named range as well). Here is step by step instruction after you initiated the query from Data->From Table/range. I tried to simplify as possible.
I tried to upload the screenshots directly to this post, but it doesn't work - something is wrong in my environment or that's due to another update of the portal over weekend. Anyway, they are in the attached Word file.
Steps are:
- Duplicate Article column for future use
- Split Article column on levels columns using comma as delimiter
- Apply Whole Number type the that columns
- Select Level 2 column, in ribbon Add Column -> Conditional Column
- Add logic: select Output as column and add Description if Level2=null
- Fill that column down to substitute null by text
- Ribbon, Add Custom column with logic
Adding to it Description if Level 3 is null and Level 2 is not null. Empty string otherwise to skip Fill down on next step for these records
- Fill that column down
- From ribbon add another conditional column – keep it with empty string if Level3 is null, otherwise Description
- Remove intermediate columns and reorder the rest as you need
- Rename your columns (double click on titles)
- Apply proper types for the columns
- Load query to the table in the desired location
- Format the table as needed and right click on the table to adjust properties not to re-format with query refresh
- As a note: usually I’d prefer to rename steps in query to make them bit more understandable and user friendly (right click on step name and rename)
Please see attached files
Thank your Sergei, with this detail I was able to reproduce your steps in my example and it is now working fine. I am having trouble now to sort by the paragraph numbering column because when I have 2 digits in some of the groups it does not order well. See example bellow. If I order this column it will join the 2.1 with 2.10 and 2.11 and I want 2.1, 2.2,, 2.3, 2.4, 2.5, 2.6, 2.7, 2.8, 2.9, 2.10, 2.11 etc.
Do you know a workaround to this? I found a userdefined function that solves this in excel but I dont know how to use userdefined functions in a Crate Custum Column step. do you know any other way?
1 1.1 1.1.1 1.1.2 2. 2.1 2.1.1. 2.2 2.2.1 2.2.2 3 3.1 3.2 3.3 3.4 3.5 3.6 3.7 3.8 3.9 3.10 3.11 3.12 3.13 3.14 3.15 3.16 3.17 Hi Joanna,
If you hierarchy column looks like this
first step duplicate it
when split new column by "." delimiter on number of columns which is definitely more than your hierarchy level (here is on 6 columns)
Select new columns and replace null on zero and nothing on zero (here is second one)
Add one more custom column which combines your levels into one number
Sort this column ascending
and after that remove all above intermediate columns. Now your list is sorted like
Thank you so much for your quick reply. For some reason I am not getting notifications on the answers I get, so I only saw your answer now. I did exactly what you suggest, by splitting the column into 6 columns, but I didn't know how to do the custom column. Could you send me the custom column formula in text so I could copy paste it into my model. Its a bit difficult to write it properly by hand.
I am not sure you can help me with another issue. I have another column with the supplier that is going to do the job for each article. It is a Data validation Column. After selecting each supplier, I filter the entire table by supplier to crate another sheet for each supplier. I did a macro for that and its working fine. When the suppliers fill the price for each article they send the list back to me and I put each file I get from each supplier in a folder to do a Get & Transform from Folder so I can combine all the buggets I get from the suppliers in one list back again.
For some reason I am getting an error when I do the Get&Transform from folder.
An error occurred in the ‘Transform File from Orçamentos’ query. Expression.Error: The key didn't match any rows in the table.
Details:
Key=Record
Table=TableFirst I thought it was the Data Validation column, but I already cleared the data validation from each file and it is still giving an error. I tried with other files and it works fine. There must be something with my files. I send you in attachment one of the files and the others are all very similar but with different data.
Thank you very much in advance,
Joana