cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Renjith Frequent Visitor
Frequent Visitor

Grouping Issue

Hi 

I need a help

 

When I loads data on power bi 

it looks like

 

Group  ID

A          1

B           2

B          3

C          4

D          5

 

My expected out put

 

A    B     C    D

1    2      4    5

nul  3    nul   nul 

 

please help on this

3 REPLIES 3
Highlighted
nandukrishnavs Frequent Visitor
Frequent Visitor

Re: Grouping Issue

Hi,

 

Try below query

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVnICsozgLGMwyxnIMgGzXIAsU6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Group " = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group ", type text}, {"ID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group "}, {{"ID table", each _, type table [#"Group "=text, ID=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "ID List", each Table.Column([ID table],"ID")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"ID List", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"ID table"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns")
in
    #"Transposed Table"

output.PNG

 

Then you can try "Split Column by Delimiter".

 

output2.PNG

 

 

Regards,

Nandu Krishna

Renjith Frequent Visitor
Frequent Visitor

Re: Grouping Issue

Thanks Nandu

But I am expecting value null in the column 1, Column 3 & Column 4 for row 3

Super User
Super User

Re: Grouping Issue

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVnICsozgLGMwyxnIMgGzXGLygGxTpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Group = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"ID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group"}, {{"Rows", each _, type table [Group=text, ID=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Add Index", each Table.AddIndexColumn([Rows], "Index", 1, 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Rows"}),
    #"Expanded Add Index" = Table.ExpandTableColumn(#"Removed Columns", "Add Index", {"ID", "Index"}, {"ID", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Add Index",{"Index", "Group", "ID"}),
    #"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"[Group]), "Group", "ID"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"

Here's your code... To get this always working, you'll probably have to also sort the table by the ID column upfront. I did not do it since it was already sorted but you might have to.

 

Best

Darek

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 45 members 1,212 guests
Please welcome our newest community members: