Forum Discussion
Helper IConsolidate rows based on primary key
I'm not sure if I know the correct way to ask this so I'll try to just show it.
| 1 | Health |
| 1 | Nutrition |
| 1 | Orientation |
| 2 | Health |
| 2 | Nutrition |
| 3 | Nutrition |
| 3 | Orientation |
Is there a way for me to create a column for each data point in column 2 or is there a better way to go about this? I want to consolidate so that I can end up with 1 row for each key.
| 1 | Health | Nutrition | Orientation |
| 2 | Health | Nurtition | |
| 3 | Nutrition | Orientation |
Thanks a lot!
Please use the below m code.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJITcwpyVCK1YFw/UpLijJLMvPz4CL+RZmpeSWJcDEjVE1GGJqMsYqgGBMLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column2] = "Health" then 1 else if [Column2] = "Nutrition" then 2 else 3), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Custom", {{"Custom", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Added Custom", {{"Custom", type text}}, "en-US")[Custom]), "Custom", "Column2") in #"Pivoted Column"Pbix attached.
That didn't do anything for me but I did figure it out perhaps the longest way possible but it worked none the less.
I grouped by primary key then created lists for each column using Table.Column. Then created an if statement to count how many nulls were in each row so I could filter by the least amount of nulls per primary key. Then I grouped by the minimum nulls and filtered out the rest.
So probably way more complex than it needed to be but like I said it worked.
9 Replies
- Ashish_Mathur
Super User
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Value", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Value]="Health" then 1 else if [Value]="Nutrition" then 2 else 3), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Custom", {{"Custom", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Added Custom", {{"Custom", type text}}, "en-IN")[Custom]), "Custom", "Value") in #"Pivoted Column"
davidgaribaldiAshish_Mathur, I think this is close but I'm still getting duplicate primary keys.
1 Health null null 1 null Nutrition null 2 null Nutrition null 2 null null Orientation rather than
1 Health Nutrition null 2 null Nutrition Orientation - mwegener
Most Valuable Professional
Hi davidgaribaldi ,
check the aggregation at your pivot step. It should be max.
Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
- mwegener
Hi davidgaribaldi ,
just as an idea. Add a column of values and pivot the two columns.
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
- v-diye-msft
Community Support
Please use the below m code.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJITcwpyVCK1YFw/UpLijJLMvPz4CL+RZmpeSWJcDEjVE1GGJqMsYqgGBMLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column2] = "Health" then 1 else if [Column2] = "Nutrition" then 2 else 3), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Custom", {{"Custom", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Added Custom", {{"Custom", type text}}, "en-US")[Custom]), "Custom", "Column2") in #"Pivoted Column"Pbix attached.
- mwegener
Hi davidgaribaldi,
has your question been answered?
Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
