Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello. I have what looks like a simple problem, but has multple dimensions to it.
Problem: I’m importing a .csv file that contains two columns; 1. People and 2. Projects but there are blank records in both columns. For example, the People column has a single entry for a Person but then there are one or more blank records until there's a new project that the previous person has no association with. At that point, there's a new Person identified and it's blank again until the Project column has a value that doesn't apply to that person. Unfortunately, I can’t just remove the blanks without impacting the other columns (EXCEPT in the case where the People record is populated and the Project record is blank – that row can be removed without impact as long as I don't lose the Person's name for the subsequent Projects. Any help or suggestions would be greatly appreciated.
BTW, I created a picture of the table, which might help to visualize the problem but I wasn't able to apply it to this post. I can email it to anyone who would like.
Thank you in advance for any help.
Solved! Go to Solution.
Hi @Anonymous,
To do what you need:
And here is M script for your reference. I've manually entered your values to be able to show result better. I've formatted needed changes in bold.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs/PT89MVdJRUorViVYC0o4whhOM4QxjuIAZIUWlydmpRUhaXMEM38SiSmzmQGQDEkuQJN2gOtJTEpFE4Ra5K8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [People = _t, Project = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"People", type text}, {"Project", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"People"}), #"Filled Down" = Table.FillDown(#"Replaced Value",{"People"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Project] <> "")) in #"Filtered Rows"
Here's the image I wanted to show. Hopefully this clears up some of the details.
Hi @Anonymous,
To do what you need:
And here is M script for your reference. I've manually entered your values to be able to show result better. I've formatted needed changes in bold.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs/PT89MVdJRUorViVYC0o4whhOM4QxjuIAZIUWlydmpRUhaXMEM38SiSmzmQGQDEkuQJN2gOtJTEpFE4Ra5K8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [People = _t, Project = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"People", type text}, {"Project", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"People"}), #"Filled Down" = Table.FillDown(#"Replaced Value",{"People"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Project] <> "")) in #"Filtered Rows"
Hi Zoloturu. Thanks for the help. This works perfect and is much easier than I thought it would be! I appreciate the images. Those were a big help. - Bingo
@Anonymous, you are welcome.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |