How do I create 3 columns and use data from other columns ?

[EaglesTony]

I have something relatively complex.

Here is my table:

Key         Parent       AllStatusCount    Status           CountForThisStatus

1             123            5                         Done             2

1             123            5                         In Progress    2

1             123            5                         Not Started   1

2             123            1                         Done             1

3             456            3                         Done             1

3             456            3                         Not Started   2

What I need is 3 individual records for eaach status, so the results are:

Key         Parent       Status           CountForThisStatus

1             123           Done             2 

1             123           In Progress    2 

1             123           Not Started   1 

2             123           Done             1

2             123           In Progress    0

2             123           Not Started   0

3             456           Done             1

3             456           In Progress    1

3             456           Not Started   2

[Fowmy]

@EaglesTony 
Should the last set be

3             456           Done             1

3             456           In Progress    0

3             456           Not Started   2

[EaglesTony]

Yes

[Fowmy · Accepted solution]

@EaglesTony 

Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MgaSpkDskp+XCqSMlGJ10KU88xQCivLTi1KLi3Go8MsvUQguSSwqSU0BiYNVGMFVGCKMh0iBBE1MzYCkMX4pVHOBNscCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Parent = _t, AllStatusCount = _t, Status = _t, CountForThisStatus = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Key", "Parent"}, {{"StatusCount", each {"Done","In Progress","Not Started"}}}),
    #"Expanded StatusCount" = Table.ExpandListColumn(#"Grouped Rows", "StatusCount"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded StatusCount", {"Key", "Parent", "StatusCount"}, 
Source, {"Key", "Parent", "Status"}, "Expanded StatusCount", JoinKind.LeftOuter),
    #"Expanded Expanded StatusCount" = Table.ExpandTableColumn(#"Merged Queries", "Expanded StatusCount", {"CountForThisStatus"}, {"CountForThisStatus"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Expanded StatusCount",null,"0",Replacer.ReplaceValue,{"CountForThisStatus"}),
    #"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Key", Order.Ascending}, {"Parent", Order.Ascending}})
in
    #"Sorted Rows"

[EaglesTony]

So then I would only need to change my source to fit my needs ?

[Fowmy]

@EaglesTony 

Yes as long as other columns are same, you may chagne them if required