Power Query M Function to loop though spreadsheet

[xonder]

I recieved a spreadsheet that has data arrange something like this:

Brand: X
Location	Units	Price	Sales
France	5	10	50
Germany	8	10	80
Austria	3	10	30
Brand: Y
Location	Units	Price	Sales
France	2	20	40
Germany	8	20	160
Austria	4	20	80

There are about 20 different brands.

How can I use an M Function to place the Brand in a separate colum and get a table that looks like this:

Location	Units	Price	Sales	Brand
France	5	10	50	X
Germany	8	10	80	X
Austria	3	10	30	X
France	2	20	40	Y
Germany	8	20	160	Y
Austria	4	20	80	Y

In excel I suppose I would use come form of looping.

Thanks for your help.

[mussaenda]

Hi @xonder ,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcipKzEuxUohQ0lFSgONYnWgln/zkxJLM/DygQGheZkkxkA4oykxOBdLBiTmpxWBFbkDdYCFTIDY0ADEMwBLuqUW5iXmVQAELmIwFRMaxtLikKDMRKGAMkzGGyECdEkmhU4xAGGSsCTangGUMzdDdYgKTArkyFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Brand", each if Text.Contains(Text.Upper([Column1]), "BRAND") then Text.Trim(Text.AfterDelimiter([Column1], ":")) else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Brand"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom", each if Text.Contains(Text.Upper([Column1]), "LOCATION") then "Brand" else [Brand]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Brand"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each not Text.Contains([Column1], "Brand")),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Location", type text}, {"Units", type text}, {"Price", type text}, {"Sales", type text}, {"Brand", type text}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([Location] <> "Location"))
in
    #"Filtered Rows1"

You can try the above code.

Result is this:

I know there are better solutions here with fewer steps.

But I hope this helps.

Thank you

[mahoneypat · Accepted solution]

Here is one way to do this in the query editor.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcipKzEuxUohQ0lFSgONYnWgln/zkxJLM/DygQGheZkkxkA4oykxOBdLBiTmpxWBFbkDdYCFTIDY0ADEMwBLuqUW5iXmVQAELmIwFRMaxtLikKDMRKGAMkzGGyECdEkmhU4xAGGSsCTangGUMzdDdYgKTArkyFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column1], "Brand") then Text.AfterDelimiter([Column1], ":") else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.Contains([Column1], "Brand:")),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Location", type text}, {"Units", type text}, {"Price", type text}, {"Sales", type text}, {" X", type text}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([Price] <> "Price")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{" X", "Brand"}})
in
    #"Renamed Columns"

Regards,

Pat

[amitchandak]

@ImkeF , can you help on this