cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nsbars Regular Visitor
Regular Visitor

How to group and pivot records exclude nulls

Hello

I have such table (a lot of records, it's just example):

screen1.jpg

 

I need to get in PBI such result:

screen2.jpg

 

Please help.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: How to group and pivot records exclude nulls

@nsbars 

 

Please check if this helps

See File attached as well for the steps

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYqVYHQgHiMwgHEMzEyDH0BwuCxFAVwBE5kqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), 
    let _t = ((type text) meta [Serialized.Text = true]) in type table [tickets_id = _t, Author = _t, Assighnee = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"tickets_id", Int64.Type}, {"Author", Int64.Type}, {"Assighnee", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"tickets_id"}, 
    {{"Author", each List.First(List.RemoveNulls([Author]))},
    {"Assighnee", each Text.Combine(List.RemoveNulls(List.Transform([Assighnee],each Text.From(_))),",") }}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Assighnee", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Assighnee.1", "Assighnee.2"})
in
    #"Split Column by Delimiter"
Try my new Power BI game Cross the River

View solution in original post

2 REPLIES 2
Super User
Super User

Re: How to group and pivot records exclude nulls

@nsbars 

 

Please check if this helps

See File attached as well for the steps

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYqVYHQgHiMwgHEMzEyDH0BwuCxFAVwBE5kqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), 
    let _t = ((type text) meta [Serialized.Text = true]) in type table [tickets_id = _t, Author = _t, Assighnee = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"tickets_id", Int64.Type}, {"Author", Int64.Type}, {"Assighnee", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"tickets_id"}, 
    {{"Author", each List.First(List.RemoveNulls([Author]))},
    {"Assighnee", each Text.Combine(List.RemoveNulls(List.Transform([Assighnee],each Text.From(_))),",") }}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Assighnee", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Assighnee.1", "Assighnee.2"})
in
    #"Split Column by Delimiter"
Try my new Power BI game Cross the River

View solution in original post

Highlighted
nsbars Regular Visitor
Regular Visitor

Re: How to group and pivot records exclude nulls

@Zubair_Muhammad 

It really works! Thank you!

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 360 members 3,196 guests
Please welcome our newest community members: