Join 2 data source as a view and transpose results turning rows in to columns

[RichardF-CRC]

I have 2 data sources from web-service APIs from a proprietary service.

Table A has the items i want to report

Table B has "field values" that i want to include but they are stored as variable types within Table B.

Table A is 1:M(optional) to Table B

I have already joined these two tables with this simple relationship.

note that not every value in A has every field in B - the rows returned in B can vary

So I get 

A.Item	B.code	B.description
001	B.field_name	Status
001	B.field_value	Open
001	B.field_name	User
001	B.field_value	Bob
002	B.field_name	Status
002	B.field_value	Closed
002	B.field_name	User
002	B.field_value	June
002	B.field_name	Priority
002	B.field_value	1

and i want to transform it in to

Item	Status	User	Priority
001	Open	Bob
002	Closed	June	1

[v-heq-msft · Accepted solution]

Hi @RichardF-CRC ,
A

Item

001

002

003

B

Item	code	description
001	B.field_name	Status
001	B.field_value	Open
001	B.field_name	User
001	B.field_value	Bob
002	B.field_name	Status
002	B.field_value	Closed
002	B.field_name	User
002	B.field_value	June
002	B.field_name	Priority
002	B.field_value	1

Chnage B with power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwVNJRctJLy0zNSYnPS8xNBXKDSxJLSouVYnXQ5csSc0pBCvwLUvOwSEO1hxanFuHR7JSfBJU1ImC1ERbdzjn5xakpuA1Ashybdq/SvFTcmgOKMvOLMksq8RhgqBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, code = _t, description = _t]),
    FieldNames = Table.SelectRows(Source, each Text.Contains([code], "field_name")),
    FieldValues = Table.SelectRows(Source, each Text.Contains([code], "field_value")),
    FieldNamesWithIndex = Table.AddIndexColumn(FieldNames, "Index", 0, 1, Int64.Type),
    FieldValuesWithIndex = Table.AddIndexColumn(FieldValues, "Index", 0, 1, Int64.Type),
    MergedTable = Table.NestedJoin(FieldValuesWithIndex,{"Item", "Index"},FieldNamesWithIndex,{"Item", "Index"},"Table",JoinKind.Inner),
    #"Expanded Table" = Table.ExpandTableColumn(MergedTable, "Table", {"code", "description", "Index"}, {"Table.code", "Table.description", "Table.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"Index", "Table.Index", "code", "Table.code"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Table.description]), "Table.description", "description")
in
    #"Pivoted Column"

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly