Populate Table by rows with same ID and distinct values in column from another table

[Anonymous]

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

[Anonymous]

@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"

[Anonymous]

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.

[Anonymous · Accepted solution]

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.

[Anonymous]

@Anonymous Thanks so much! I was trying to mess around with pivot columns earlier, but did not quite understand how it worked. Adding the extra column was the key! Thanks again.