Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Parse JSON Column with the Relational Key Value as an Attribute

Greetings all,    Problem: I am currently attempting to ingest data into our Power BI ecosphere by building a dataflow connected to an Odata source.  Connection is set up and functioning correctly...
Need Help
dufoq3's avatar
dufoq3
Community Champion
2 years ago

Anonymous,

 

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSi/NTFHSQaOqg0uTHEtKijKTSktSDa1AojrIQkZWxUBWXjqKoDFUsBZoAAjF6uAwnSoW4DOdcgtiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ObjectID = _t, Attribute1 = _t, Attribute2 = _t, GUID1 = _t, GUID2 = _t, GUID3 = _t]),
    GuidColumns = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_,"GUID")),
    StepBack = Source,
    ReplacedGuidColumns = Table.ReplaceValue(StepBack,"",null,Replacer.ReplaceValue, GuidColumns),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(ReplacedGuidColumns, List.Select(Table.ColumnNames(ReplacedGuidColumns), each not List.Contains(GuidColumns, _)), "TransformAttribute", "Value")
in
    #"Unpivoted Other Columns"
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thank you dufoq3  for your very thorough response.  Unfortunately, I did a poor job with the example and because of that, your solution is too literal.  For example, your code is searching for the literal value of 'GUID' and then unpivoting the columns accordingly.  The variables GUID1, GUID2 and GUID3 are actually real GUID values that are dynamic (e.g., unknown to me at the time the query compiles).  They are actually IDs to another object that I am pulling in separately.  I will be uploading a better example shortly with a more representative dataset example.  Thanks again.