Combine rows by ID and transpose columns

Question

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 Answer

Hi&nbsp;lightw0rks&nbsp;,
&nbsp;
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"
&nbsp;
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"
&nbsp;
then just follow the steps above using this new column

Forum Discussion

Combine rows by ID and transpose columns

2 Replies

Related content

Help Transposing

Combine Column

Transpose/ Transform Rows to Columns

Combining Column Data

Combine columns into 1