Help with CSV import

[Anonymous]

Hello guys,

I have the following CSV table:

Cod Product	Product
Category	X
31	Water
Category	Y
41	Snack
42	Chocolat
Category	Z
51	Rice
52	Bean
53	Salad

I wanna import this CSV table to Power BI but it has to look something like this:

Cod Product	Product	Category
31	Water	X
41	Snack	Y
42	Chocoalate	Y
51	Rice	Z
52	Bean	Z
53	Salad	Z

Somebody can help me?

obs: i can't edit the original csv table

[mussaenda]

Hi @Anonymous,

Here's another way.

You can try this on Power Query, Advanced Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sSU3PL6pUUNJRilCK1YlWMjYEMsOBwkVgLkhCAcyCKQUKRIIFTEAqg/MSk7MhXCMg1zkjPzk/J7EEt94osIApSG9QZnIqhAfS6pSamAfhGYPMTcxJTFGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Cod Product" = _t, Product = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cod Product", type text}, {"Product", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Category", each if Text.Contains(Text.Upper([Cod Product]), "CATEGORY")
then [Product] 
else null, type text),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Category"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.Contains([Cod Product], "Cat") and [Cod Product] <> "" and ([Cod Product] <> " "))
in
    #"Filtered Rows"

Hope this helps.

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Cod Product] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Category", each if [Cod Product] = "Category" then [Product] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Category"}),
    #"Added Custom2" = Table.AddColumn(#"Filled Down", "Value", each if [Cod Product]<>"Category" then [Cod Product] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Cod Product"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([Value] <> null)),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"Category", "Product", "Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Value", type number}})
in
    #"Changed Type1"

[mahoneypat · Accepted solution]

This is one way to do this in the query editor. To see how it works, simply 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("i45Wck4sSU3PL6pUUNJRilCK1YlWMjYEMsOBwkVgLkhCAcyCKQUKRIIFTEAqg/MSk7MhXCMg1zkjPzk/J7EEt94osIApSG9QZnIqhAfS6pSamAfhGYPMTcxJTFGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Cod Product" = _t, Product = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cod Product", type text}, {"Product", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Cod Product", Text.Trim, type text}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "Category", each if [Cod Product] = "Category" then [Product] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Category"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Cod Product] <> "" and [Cod Product] <> "Category")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Category", type text}})
in
    #"Changed Type1"

Best regards

Pat