Insert rows based on text in column with Power Query

[EnnyKraft]

I need to create a file my database publishing software can use but the export from the database is not exactly what I need. I have created a sample file so that you can understand better has to be changed. This is what the export looks like:

But it should look like this:

Filling the empty cells in the Products is easy. But I haven't got a clue how to convert those rows where Groups and Subgroups aren't empty into rows of their own. My first guess was using Table.InsertRows but I can't figure out how to use it since the position isn't regular by any means.

If anybody would like to download my sample file, here's the link:
Download from Box.com

I would be really grateful if someone could help me since the original file contains thousands of rows and doing this manually would take ages.

 

[sandy666]

Hi Zbyszek,
it's tailored to example from post#1
I don't want to think what will be if will be ? = post representative example

 

[billszysz]

Hi Zbyszek,
it's tailored to example from post#1
I don't want to think what will be if will be ? = post representative example

All in all you are right ?

 

[sandy666]

All in all you are right ?

but if you've an idea how to standardize M for more non-standard (like yours, with no pattern) examples - be my guest

 

[billszysz]

Ok, so this is only for structure like in author's example....

let
    fnCombine = (t as table) =>
        let
            fnDoIt = (t as table) =>
                let
                    #"Filled Down1" = Table.FillDown(t,{"Subcategory"}),
                    #"Grouped Rows1" = Table.Group(#"Filled Down1", {"Subcategory"}, {{"tbl2", each Table.RemoveColumns(_, {"Subcategory"}), type table [Product=text, Quantity=text, SKU=number]}}),
                    #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "first", each Table.FromColumns({{[Subcategory]}},{"Product"}) & [tbl2]),
                    Custom1 = Table.Combine(#"Added Custom"[first]),
                    #"Filled Down2" = Table.FillDown(Custom1,{"Product"})
                in
                    #"Filled Down2",

            #"Removed Columns" = Table.RemoveColumns(t,{"Category"}),
            Check = if List.NonNullCount(#"Removed Columns"[Subcategory]) = 0 then  Table.FillDown(#"Removed Columns"[[Product],[Quantity],[SKU]], {"Product"}) else fnDoIt(#"Removed Columns")
        in
            Check,

    Source = Excel.CurrentWorkbook(){[Name="RawData"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Subcategory", type text}, {"Product", type text}, {"Quantity", type text}, {"SKU", Int64.Type}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Category"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Category"}, {{"tbl1", each _, type table [Category=text, Subcategory=text, Product=text, Quantity=text, SKU=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "ToCombine", each Table.FromColumns({{[Category]}},{"Product"}) & fnCombine([tbl1])),
    Combine = Table.Combine(#"Added Custom"[ToCombine])
in

    Combine

Cheers sandy

 

[sandy666]

so this is only for structure like in author's example....

I knew that
thanks for sharing M

 

[EnnyKraft]

Ok, so this is only for structure like in author's example....

I ran the code with your example and it worked fine. These weren't my real data anyways but it showed me what I needed to do and I could adapt my code to get the desired results. It's the exact opposite of what we usually want our data to look like for Excel but this will be further transformed with dynamic array functions and then imported into InDesign with a database publishing plugin to create large catalogues.
But thank you very much for going through the trouble to create an alternative code! I will try to figure out what you did but unfortunately I'm a total beginner when it comes to M code. I just learned about (t as tabel) => in Mike Girvins's new video (inspired by you I believe) and it will be great to give it a try.

 

[citizenbh]

In post #5 change in:

RAR = Table.AlternateRows(IF2,16,1,3),

with

RAR = Table.AlternateRows(IF2,16,0,3),

 

[EnnyKraft]

In post #5 change in:

RAR = Table.AlternateRows(IF2,16,1,3),

with

RAR = Table.AlternateRows(IF2,16,0,3),

Yep, you're correct. I didn't spot that since I wasn't sure what Table.AlternateRows does but by entering different values and seeing the results I finally managed to understand it.

 

[sandy666]

result with: RAR = Table.AlternateRows(IF2,16,0,3), is incompatible with post#1 (expected result)