Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Help with CSV import

Hello guys,

 

I have the following CSV table:

Cod ProductProduct
Category X
31Water
  
CategoryY
41Snack
42Chocolat
  
CategoryZ
51Rice
52Bean
53Salad

 

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

Cod ProductProductCategory
31WaterX
41SnackY
42ChocoalateY
51RiceZ
52BeanZ
53SaladZ

 

Somebody can help me?

 

obs: i can't edit the original csv table

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    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"

  • mussaenda's avatar
    mussaenda
    Community Champion
    5 years ago

    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.