Transpose? Query...

[Anonymous]

Hello everyone, 

Im trying to work out how to get related rows of data into separate columns. To explain, please see first table below. This is how my data is currently structured.

Houshold ref	Ref#	First	Last	Age	Date child under 18 started school
V1865	1865	Parents 1	Name	33
V1865	1865	Parent 2	Name	24
V1865	1865	Child 1	Name	3	30/01/2019 00:00
V1865	1865	Child 2	Name	8	04/02/2019 00:00

What I am trying to do is structure this data to perform a mail merge for each parent (not for the children). So i thought that i need to have all the relevant data for each parent on that parent's row. The problem is is that the children within the family are relevant data for each parents, so i need to transpose multiple columns. For example, for each parent I need to know when each child started school, as well as their name and age. So my result will (I think) have to look like this:

Houshold ref	Ref#	First	Last	Age	Child 1 Name	Child 2 Name	Child 1 Age	Child 2 Ag2	Date Child 1 started school	Date child 2 started school
V1865	1865	Parents 1	Name	33	Child 1	Child 2	3	8	30/01/2019 00:00	04/02/2019 00:00
V1865	1865	Parent 2	Name	24	Child 1	Child 2	3	8	30/01/2019 00:00	04/02/2019 00:00

However this seems like overly complicated. 

Naturally households can also have more than 2 children....

Any advise on the best way that I should be going about this?

[v-yingjl · Accepted solution]

Hi @Anonymous ,

All actions are like the following codes in Advanced editors and I'll help you to understand it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjO0MDNV0lGCUgGJRal5JcUKhkC2X2JuKpAyNgYSSrE6ONUaIdQameBQ65yRmZOCYioIG+gbGOobGRhaKhgYWBkY4NaIZIUFEBuY6BsYoWiMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Houshold ref" = _t, #"Ref#" = _t, First = _t, Last = _t, Age = _t, #"Date child under 18 started school" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Houshold ref", type text}, {"Ref#", Int64.Type}, {"First", type text}, {"Last", type text}, {"Age", Int64.Type}, {"Date child under 18 started school", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Age] > 18),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Houshold ref"},Table.SelectRows(#"Changed Type", each [Age] <= 18), {"Houshold ref"}, "Child", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each let #"Removed Columns" = Table.RemoveColumns([Child],{"Houshold ref", "Ref#", "Last"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Index"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"First", type text}, {"Age", Int64.Type}, {"Date child under 18 started school", type text}, {"First_1", type text}, {"Age_2", Int64.Type}, {"Date child under 18 started school_3", type datetime}})
    in #"Changed Type1"),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"First", "Age", "Date child under 18 started school", "First_1", "Age_2", "Date child under 18 started school_3"}, {"Custom.First", "Custom.Age", "Custom.Date child under 18 started school", "Custom.First_1", "Custom.Age_2", "Custom.Date child under 18 started school_3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Child"})
in
    #"Removed Columns"

1. Filter the parents whose age is over 18:
2. Filter the child whose age is equal or less than 18 and merge table with step1:
3. Create custom columns and remove some repeated columns:
4. Expand the previous custom columns:
5. Remove repeated columns:
6. Rename the new columns
7. You will get the final expected result:

All the steps can be seen in Applied steps in Power Query Editor. In addtion, here is the sample file hopes to help you: PBIX 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks very much for your support, Yingjie.

I think i can follow most of your solution so I will try and replicate today. Good reason to learn more about coding in the editor 🙂 

[luapdoniv]

Not sure how you are identifying the parent and child from Name.
If there be a way for it, I would consider creating a duplicate and filtering out one table for parent and another for child.
A unique of ref# table should help create the relation between the tables and you can put them across in a matrix visual.

Again just a quick thought. Will need to see the data sample to come up with a solution. 

[Anonymous]

Thanks for your reply.

Sorry, should have mentioned - the families are linked with a family reference number. So i will delineate children by age. I'll try the two table and see if a matrix will work.

[amitchandak]

@Anonymous , Refer, if this can help

https://yodalearning.com/tutorials/power-query-helps-transposing-data/