Combine rows by ID and transpose columns

[lightw0rks]

I have the following table, where A = a number and B = some text

ID	DataType	Value
1	Number	A
1	Text	B
2	Number	A
2	Text	B
3	Number	A
3	Text	B

I would like the followng output:

ID	Number	Text
1	A	B
2	A	B
3	A	B

I have tried using Group by and transposing the columns, as well as trying to create a custom column but have not been successful. Any help would be appreciated.

[MFelix · Accepted solution]

Hi @lightw0rks ,

If you have the table as presented you need to:

• Select Data Type column
• Transform
• Pivot Columns
• Use values of colum Text
• Don't aggregate

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIrzU1KLQIyDJVidSBCIakVJUDKCSxghKnGCF2NMaYaYxQ1sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, DataType = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"DataType", type text}, {"Value", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[DataType]), "DataType", "Value")
in
    #"Pivoted Column"

If you don't have the datatype column you need to add the a calculated column with the following code:

if (try Number.From([Value]) otherwise "Text" ) = "Text" then "Text" else "Number"

then just follow the steps above using this new column

[lightw0rks]

Thanks MFelix, works great!