Forum Discussion
Populate Table by rows with same ID and distinct values in column from another table
Hello,
I am trying to create a new table that has unique IDs and populates a column depending on the distinct values from another table and can't quite figure it out. I have a SQL statement that displays all IDs that have 2 or more distinct values in another column and could invert it to get the rest, but I do not know how to populate a table in the fashion in my example.
Current data is structured in this format:
| ID | Value |
| A | 1 |
| A | 1 |
| A | 2 |
| A | 1 |
| B | 2 |
| B | 2 |
| B | 2 |
| ... | ... |
I am trying to create a new table with the information above to be viewed like this:
| ID | Value 1 | Value 2 |
| A | Y | Y |
| B | N | Y |
| ... | ... | ... |
Thanks
Sorry, that creates the little sample. Perhaps it would be better to do the following in the interface:
1. Create a dummy column with Custom Column. Give it the value 1
2. Pivot the Value column. That will create a column for each value in the column. Use the Dummy column, because it will disappear.
3. Select all of the new columns and convert to True/False.
4. Keep all of the new columns selected and Replace Values: null with False.
4 Replies
Anonymous - You could do something like the following in Power Query.
Notes: The last 2 steps (#"Replaced Value" and #"Changed Type1") refer to the 2 columns, 1 and 2. If there are more values, it would also need to include those.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitVBZxmhiTnBxTBYsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 1),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Custom", {{"Value", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Added Custom", {{"Value", type text}}, "en-US")[Value]), "Value", "Custom", List.Sum),
#"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"1", type logical}, {"2", type logical}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,false,Replacer.ReplaceValue,{"1", "2"})
in
#"Replaced Value"Hi Anonymous , thanks for the response.
If I'm pulling from a table that is already imported how do I maninuplate this line?
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitVBZxmhiTnBxTBYsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Value = _t])I replaced Table with the name of my table that was imported and it autocorrects it to #"TableName".FromRows....
and I get an error saying token comma expected after the table name.
Sorry, that creates the little sample. Perhaps it would be better to do the following in the interface:
1. Create a dummy column with Custom Column. Give it the value 1
2. Pivot the Value column. That will create a column for each value in the column. Use the Dummy column, because it will disappear.
3. Select all of the new columns and convert to True/False.
4. Keep all of the new columns selected and Replace Values: null with False.