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

Question

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

anonymous · Accepted Answer

Hi&nbsp;RichardF-CRC&nbsp;,A

Item

001

002

003

B
&nbsp;

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

&nbsp;
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

&nbsp;
Best regards,Albert He
If this&nbsp;post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly
&nbsp;
&nbsp;

Forum Discussion

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

1 Reply

Related content

Help Transposing

Transpose data

Turn off navicagtion pane

Transpose Title Row ONLY

Transpose/ Transform Rows to Columns

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