Forum Discussion

mtiller12's avatar
mtiller12
New Member
4 years ago
Solved

Working with mixed data

I am struggling to manipulate a data source that has disparate data fields grouped into single columns and tagged with corresponding unique recordids.  Below is an example dataset of what I have comi...
Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdA7DsMgEATQq1hbuwh/TmHJhQsLUSCFOhFy7m8ShDaIqVjNsq+YEEg+hKOVzpxKfQTFNdAu6rh/Urnyf/j9umyvK9fseD/Tb2jLUZHtQA5KD6EigaLagRqUHkJFAUW3Az0oPYSK7opnxaBeDPfiJ8UAxaJeLPcyKxYoDvXiuJdZcUDxqBfPvcxKXcYb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fieldvaluetext = _t, fieldname = _t, recordid = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fieldvaluetext", type text}, {"fieldname", type text}, {"recordid", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[fieldname]), "fieldname", "Fieldvaluetext")
in
    #"Pivoted Column"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

lbendlin's avatar
lbendlin
Super User
4 years ago
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdA7DsMgEATQq1hbuwh/TmHJhQsLUSCFOhFy7m8ShDaIqVjNsq+YEEg+hKOVzpxKfQTFNdAu6rh/Urnyf/j9umyvK9fseD/Tb2jLUZHtQA5KD6EigaLagRqUHkJFAUW3Az0oPYSK7opnxaBeDPfiJ8UAxaJeLPcyKxYoDvXiuJdZcUDxqBfPvcxKXcYb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fieldvaluetext = _t, fieldname = _t, recordid = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fieldvaluetext", type text}, {"fieldname", type text}, {"recordid", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[fieldname]), "fieldname", "Fieldvaluetext")
in
    #"Pivoted Column"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".