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 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
Solved! Go to 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 - 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.
@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.
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |