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.

 

[truebluewoman]

I downloaded your file and named the range that contains your original information "RawData".

If you pull that into your Power Query editor then paste the following script into the "Advanced Editor" area, you should get what you need.

let
    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",{"Product"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filled Down", {"Quantity", "SKU"}, "Attribute", "Value"),
    #"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Other Columns",{"Attribute", "Value", "Quantity", "SKU"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Product"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Revised Quantity", each if Text.Contains([Product], "Product") then [Quantity] else null),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Revised SKU", each if Text.Contains([Product], "Product") then [SKU] else null),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Quantity", "SKU"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Revised Quantity", "Quantity"}, {"Revised SKU", "SKU"}})
in
    #"Renamed Columns1"

 

[sandy666]

a bit shorter

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FillD = Table.FillDown(Source,{"Product"}),
    UOSC = Table.Unpivot(FillD, {"Category", "Subcategory", "Product"}, "Attribute", "Value"),
    IF1 = Table.AddColumn(UOSC, "Custom", each if Text.Contains([Value], "Group") then "" else if Text.Contains([Value], "group") then "" else [Quantity]),
    IF2 = Table.AddColumn(IF1, "Custom.1", each if Text.Contains([Value], "Group") then "" else if Text.Contains([Value], "group") then "" else [SKU]),
    TSC = Table.SelectColumns(IF2,{"Value", "Custom", "Custom.1"}),
    Rename = Table.RenameColumns(TSC,{{"Value", "Product"}, {"Custom", "Quantity"}, {"Custom.1", "SKU"}})
in
    Rename

btw you've error in your source data : SubgroubB ==> SubgroupB

 

[sandy666]

or a bit bit shorter ?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FillD = Table.FillDown(Source,{"Product"}),
    UOSC = Table.Unpivot(FillD, {"Category", "Subcategory", "Product"}, "Attribute", "Value"),
    IF1 = Table.AddColumn(UOSC, "Custom", each if Text.Contains([Value], "roup") then "" else [Quantity]),
    IF2 = Table.AddColumn(IF1, "Custom.1", each if Text.Contains([Value], "roup") then "" else [SKU]),
    TSC = Table.SelectColumns(IF2,{"Value", "Custom", "Custom.1"}),
    Rename = Table.RenameColumns(TSC,{{"Value", "Product"}, {"Custom", "Quantity"}, {"Custom.1", "SKU"}})
in
    Rename

 

[sandy666]

I noticed my mistake so here is a proper result

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FillD = Table.FillDown(Source,{"Product"}),
    UOSC = Table.Unpivot(FillD, {"Category", "Subcategory", "Product"}, "Attribute", "Value"),
    IF1 = Table.AddColumn(UOSC, "Custom", each if Text.Contains([Value], "roup") then "" else [Quantity]),
    IF2 = Table.AddColumn(IF1, "Custom.1", each if Text.Contains([Value], "roup") then "" else [SKU]),
    RAR = Table.AlternateRows(IF2,16,1,3),
    TSC = Table.SelectColumns(RAR,{"Value", "Custom", "Custom.1"}),
    Rename = Table.RenameColumns(TSC,{{"Value", "Product"}, {"Custom", "Quantity"}, {"Custom.1", "SKU"}})
in
    Rename

 

[EnnyKraft]

Wow! That worked perfectly. When entering new date the refresh worked without a glitch. Thank you so much! ? Now all I need to do is add an extra step which adds some identifier to the Groups and Subgroups texts since my real data aren't named with any logical pattern and my understanding is that the IF-steps need to find a pattern for the names.
Thank you very, very much! This has helped me a lot!!!

 

[sandy666]

after source (as first step) add prefix ID to each column you need
Transform - Format - Add Prefix
or , as you said a few IFs ?

 

[EnnyKraft]

Managed to edit your code for my real data and add the necessary steps. It worked! This was the first time writing M code since I'm a graphics designer and not a programmer so it was great to learn how to do this. The only part I didn't understand was the Table.AlternateRows. What does that do? Quite often the M formula language guide by Microsoft leaves me totally baffled.
Thank you very much! I am really grateful for your fantastic help!

 

[sandy666]

You are welcome

Table.AlternateRows - PowerQuery M

Learn more about: Table.AlternateRows

docs.microsoft.com

 

[billszysz]

sandy, are you sure that everything is ok with your query? Try this data below. please

Category	Subcategory	Product	Quantity	SKU
GroupA		Product1	5 pcs	48956
			10 pcs	65535
GroupB	SubgroupA	Product2	20 pcs	39512
		Product3	50 pcs	57262
		Product4	1 pcs	81068
		Product5	1 pcs	29621
			5 pcs	61918
			10 pcs	24788
GroupC	SubgroupA	Product6	1 pcs	71058
		Product7	5 pcs	68562
			10 pcs	68948
			6 pcs	55124
			11 pcs	69854
GroupC	SubgroubB	Product8	5 pcs	58184
			10 pcs	44528
GroupD		Product123	5 pcs	58184
			10 pcs	44528