Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
mussaenda
Super User
Super User

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
Super User
Super User

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"

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.