Is it possible to Ungruoup one column into 3 columns using get & transfrom

[Joana Villas-Boas]

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

[#2 · Accepted solution]

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

[#3]

Hi! 

Please have a look with this. 

And please don't forget to comment.

Thanks in advance.

JBC$61892

[#4]

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?

[#5]

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

[#6]

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

[#7]

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

[#8]

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

[#9]

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=Table

First 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

[#10]

Hi Joana,

First question.

No problem, I'll find your file and send the script together with the sample.

Second question.

It will be great if you send couple of short files with dummy data and the script with which you are combining them.

[#11]

This is the script I am using to try to do what you suggest above with the 5 chapters I have. Add Custom Column = [#"CAP1"]*10000 + [#"CAP2"] * 1000 +[#"CAP3"] * 100 + [#"CAP4"] * 10 + [#"CAP5"] but it's giving an error.

I already sent you one dummy file and here goes another and the script I am using to combine these 2 files from a folder is:

let
    Source = Folder.Files("C:\Users\Joana\Documents\Orçamentos"),
    #"Invoke Custom Function1" = Table.AddColumn(Source, "Transform File from Orçamentos", each #"Transform File from Orçamentos"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Orçamentos"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Orçamentos", Table.ColumnNames(#"Transform File from Orçamentos"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Index", type any}, {"Letra", type any}, {"CAP1", type any}, {"CAP2", type any}, {"CAP3", type any}, {"CAP4", type any}, {"CAP5", type any}, {"Artigo", type any}, {"Group", type any}, {"Sub-Group", type any}, {"DESIGNAÇÃO", type any}, {"QT", type any}, {"UN", type any}, {"Fator", type any}, {"Fornecedor", type any}, {"Preço/Unitário", type any}, {"Parcial", type any}, {"Total Custo", type any}, {"Observações", type any}})
in
    #"Changed Type"

All my files are in the c:\documents\orçamentos folder.

Thanks a lot for all your help, I hope I can be of some help to you sometime.

[#12]

Joanna,

Let do step by step. Here is the script and sorting file based on your initial sample. Initially I tried to avoid any coding and use only UI to generate the script, but very short manual combining of formulas splitting and sorting through the list makes things much easier - we don't depend on number of dots in Articles.

This part is commented:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DefaultTypes = Table.TransformColumnTypes(Source,{{"Article", type text}, {"Description", type text}, {"Value", Int64.Type}}),
    DuplicateArticles = Table.DuplicateColumn(DefaultTypes, "Article", "ArticleCopy"),
    SplitArticles = Table.SplitColumn(DuplicateArticles, "ArticleCopy",
        Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Article.1", "Article.2", "Article.3"}),
    LevelsToNumbers = Table.TransformColumnTypes(SplitArticles,
        {{"Article.1", Int64.Type}, {"Article.2", Int64.Type}, {"Article.3", Int64.Type}}),
    AddLevel1 = Table.AddColumn(LevelsToNumbers, "Custom1", each if [Article.2] = null then [Description] else null ),
    FillLevel1 = Table.FillDown(AddLevel1,{"Custom1"}),
    AddLevel2 = Table.AddColumn(FillLevel1,
        "Custom2", each if [Article.3] = null then
            if [Article.2] <> null then [Description] else ""
            else null),
    FillLevel2 = Table.FillDown(AddLevel2,{"Custom2"}),
    AddLevel3 = Table.AddColumn(FillLevel2, "Custom3", each if [Article.3] = null then "" else [Description] ),
    RemoveTempColumns = Table.SelectColumns(AddLevel3,{"Value", "Article", "Custom1", "Custom2", "Custom3"}),
    ReorderColumns = Table.ReorderColumns(RemoveTempColumns,{"Article", "Custom1", "Custom2", "Custom3", "Value"}),
    RenameColumns = Table.RenameColumns(ReorderColumns,{
        {"Custom1", "Group"},
        {"Custom2", "sub-group"},
        {"Custom3", "Description"}}),
    ProperColumnsType = Table.TransformColumnTypes(RenameColumns,{
        {"Article", type text},
        {"Group", type text},
        {"sub-group", type text},
        {"Description", type text}
    }),

    // Here we convert each Article into the list, like
    // "3.2.11" => {"3","2","11"}
    ArticelToList = Table.AddColumn(ProperColumnsType, "Custom", each Text.Split([Article],".")),

    // Next by Text.PadStart add zeroes to the start of each list element to have totally 4 symbols
    // and transform it into one text string, like
    // {"3","2","11"} => {"0003","0002","0011"} => "000300020011"
    ListToSortString = Table.TransformColumns(ArticelToList,
        {"Custom", each Text.Combine(List.Transform(_, each Text.PadStart(_,4,"0")), ""), type text}),

    // Sort by this column and remove it then
    SortAscending = Table.Sort(ListToSortString,{{"Custom", Order.Ascending}}),
    AndRemoveIt = Table.RemoveColumns(SortAscending,{"Custom"})
in
    AndRemoveIt

File is attached.

Please not your resulting table is sorted by PowerQuery on each refresh if only you set External Table properties (right click on it and check menu) as this

[#13]

Fantastic, it worked! Many thanks. When you figure out why the combine files from folder is not working, please let me know. I lost a hole day today trying to figure it out and no luck. Thanks a lot. You've been a great help!

[#14]

Joana, with this method you shall same name for the tables in all files you combine (or same name for sheets if you combine by sheets).

When you combine files you have the prompt "Select the object to be extracted from each file", take it literally.

Script takes the table, let say "MyTable", from each file in the list and combines them. Or "MySheet" if you combine by sheets. No other tables/sheets. If it doesn't find such you have an error. 

You shall use same names or another logic to combine.

[#15]

Thank you so much Sergei, that was exactly the issue. I didn't know it was important to have the same name on every table! Fantastic!!! I dont know how to thank you for so much help!

[#16]

Hi Sergei,

My model is almos finished. You helped me a lot with creating a list in the format I needed. After that I used Get&Transform to append the total number of rows 4 times, so each item could be attributed to 1,2, 3 or 4 suppliers. So I added a column to the result of our query that says supplier with a Data Validation that gets a list from all suppliers. (I know that's not a very good Idea because if the query gets refreshed the nr of rows can change and the suppliers can get all mixed up)

The only manual work the user has to do is to select the suppliers for each item and then a Macro creates one sheet by supplier that will be protected and sent to each supplier for them to fill prices and quantities. It's working great.

Now my client ask me if it is possible to have a subtotal row in our query bellow each item that repeats 4 times. 3.1.1 for exemple will apear 4 times in our result query and they want a subtotal before that. If we use group on the query editor we will loose the detail refight? I only know how to do sub-totals in the result pivot table, but not the query.

Thank you in advance for your help and patience with all my questions.

Regards, Joana

[#17]

Hi Joana,

Raw idea is to create separate query with subtotals, merge it with main one before sorting (generating proper columns to merge), sort after that.

I could play with that in couple of days, on business trip now.

Regards,

Sergei

[#18]

Something like attached