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,
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
- Hello Sergey, this is exactly what I needed, but I am having a hard time using it in my real and much more complicated scenario. I would love to know the real steps you took for each situation. I am a begginer at Get & Transform and I only know how to use the buttons on the 3 tabs of the Get & Transform window. Did you do these steps using this buttons or you use code to do them? Is there way to sort by 1., 1.1, 1.2, 1.2.1, 2 etc?
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
