Power Query Column

[Raul]

Hi community!

I am trying to create in Power Query 3 columns for an imported text file which gives me this result:

Column1	Column2	Column3
Local1
	84555600
		1
	84333500
		1
Local2
	84500500
		2
	84300300
		1
	84200200
		3
	84600600
		1
----	----	----

And I want it to be shown in this way:

Locals	CodItem	Qty
Local1	84555600	1
Local1	84333500	1
Local2	84500500	2
Local2	84300300	1
Local2	84200200	3
Local2	84600600	1

How can I create these new columns from existing ones?
Thank you very much for your help.

[selimovd · Accepted solution]

Hey @Raul ,

you can get that by a combination of the "Fill" --> "Down" feature and the removal of the additional rows:

Check my example, that should explain it to you:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8slPTswxVNJRAqFYnWgQbWFiampqZmCAJAREhghpY2NjU+zSYOOMMIwzMMBQb4RknIGBMR7bjAwMjNCljRHSQIdic2ssAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3"}),
    #"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "Delte?", each if [Column2] = null and [Column3] = null then "delete" else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Column1"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([#"Delte?"] <> "delete")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Delte?"}),
    #"Filled Down1" = Table.FillDown(#"Removed Columns",{"Column2"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down1", each ([Column3] <> null))
in
    #"Filtered Rows1"

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Raul]

Great! Works perfectly, thank you very much