Forum Discussion
Is it possible to Ungruoup one column into 3 columns using get & transfrom
I have a table with a buget for works in a house like the one bellow. There is one column used to all descriptions, incluinding grouping items like this:
Article Description Value
1. Sitting Room
1.1 Painting
1.1.1 Wall € 10
1.1.2 Ceiling € 20
2. BedRoom
2.1 Painting
2.1.1 Wall € 40
2.2.2 Ceiling € 50
I want to be able to "unpivot" this list like this:
Article Group sub-group descritpion value
1. Sitting Room
1.1 Sitting Room Painting
1.1.1 Sitting Room Painting Wall € 10
1.1.2 Sitting Room Painting Ceiling € 20
2. BedRoom
2.1 BedRoom Painting
2.1.1 BedRoom Painting Wall € 40
2.2.2 BedRoom Painting Ceiling € 50
Is this possilbe? It would be also very nice if we could sort by paragraph numbering line the article. I already suggested this in uservoice.
Thanks and regards, Joana
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
Hi!
Please have a look with this.
And please don't forget to comment.
Thanks in advance.
JBC$61892
- Hello Joey, Thanks for your file but I didn't quit understand your point here. Its a bit different from what I want or I am missing something. Thanks anyway. Joana
