Rows to Column

[Mahadevaraobc]

Hi, 

Iam looking to move some columns to rows in power bi based on a search value, not sure how to do it. Please help.

Explaining my issue in detail.

I have 2 tables which has unique ID..

Table 1 contains Unique ID and Product No

Table 2 contains just unique ID

Now i require a formula where Unique ID in Table 2 should be checked in Table 1 and if present all Product Nos in Table 1 should be moved to rows in Table 2.

Table 1
Unique ID	Product No
5020048146-501	RM2-5692-000CN
5020049481-501	RU8-2592-000CN
5020049481-501	B5L24-00035
5020049481-501	J8J61-60001
5020049481-501	B5L47-67904
5020049481-501	F1J60-67926
5020049481-501	RM2-1256-000CN
5020049481-501	RU8-2592-000CN
5019961246-501	RM2-7910-000CN
5019404984-501	RM2-1256-000CN
5019877065-501	5851-6712
5019877065-501	5851-7202
5019877065-501	B5L47-67901
5019877065-501	B5L47-67907
5019877065-501	5851-6712
5019877065-501	G1W39-67912
5019877065-502	B5L47-67907
5019877065-502	5851-7202

Table 2

Unique ID

5020048146-501

5020049481-501

5019877065-501

5019877065-502

Expected Result
Unique ID	Product No1	Product No2	Product No3	Product No4	Product No5
5020048146-501	RM2-5692-000CN
5020049481-501	RU8-2592-000CN	B5L24-00035	J8J61-60001	B5L47-67904	F1J60-67926
5019877065-501	5851-6712	5851-7202	B5L47-67901	B5L47-67907	G1W39-67912
5019877065-502	B5L47-67907	5851-7202

[Anonymous]

Hi Mahadevaraobc,

You could achieve this in bringing Matrix visual to pivot all the rows into columns.

Regards,

Pradeep

[Mahadevaraobc]

This will only show unique i would like to get all, alse it should display product no and not just count...

[PattemManohar · Accepted solution]

@Mahadevaraobc  You need to transform your data a bit (in this case your main table) as below in Power Query Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndDLCsJADAXQf5l1B3LTPCZbBYWiLgRxUfr/v+EULFLpA13nTO7c9H1SYiIpEMtKSE26XzmrBWciOt7S0EwmqprMo2TWbXPQC8sIWl0GXekM2arA6gbxbB4ky+CEzmgEbCv/rF3Aaj93QYSBZzfxAH0ZqYuKbGUhijuZvo0WrY0dvDF2ppXx5x7YA/5X/hnPNsb3S4D3AnjeYHgB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, ProductNo = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique ID", type text}, {"ProductNo", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Unique ID"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Product"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Unique ID"}, {{"AllRows", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([AllRows],"Product")),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom",{{"Custom", "Product"}}),
    #"Extracted Values" = Table.TransformColumns(#"Renamed Columns1", {"Product", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns1" = Table.RemoveColumns(#"Extracted Values",{"AllRows"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns1", "Product", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Product.1", "Product.2", "Product.3", "Product.4", "Product.5", "Product.6", "Product.7"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Product.1", type text}, {"Product.2", type text}, {"Product.3", type text}, {"Product.4", type text}, {"Product.5", type text}, {"Product.6", type text}, {"Product.7", type text}})
in
    #"Changed Type1"

This will bring you the output as below

Then you need to filter this by using your Lookup UniqueIDs list. This can be done either in Power Query Editor or DAX. As we are alredy in Power Query Editor, I've done this step in Query Editor itself as below using Merge Queries option.

let
    Source = Table.NestedJoin(Test251Main,{"Unique ID"},Test251Lkp,{"Unique ID"},"Test251Lkp",JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Test251Lkp"})
in
    #"Removed Columns"

[Anonymous]

Have you tried Show blank values in your axis and value columns?

Regards,

Pradeep

[Mahadevaraobc]

Where can i get this?

[Anonymous]

You have Rows and Columns of your Matrix on the Fields area. Click on the arrow to select this.

Regards,

Pradeep