Forum Discussion
Two rows in different levels in one line Power Query
There ia a table with a lot of id. And 2 form where entered date.
Ned to copy date in one line (according to "subject" and "visit") and date_2 closest to date, because "visit" may have similar name 😞
Please find attached
result
| id_protocol | id_site | name | id_date_form | visit | date_1 | date_2 |
| 1 | 12 | 12-001 | 1 | visit 1 | 10.01.2024 | |
| 1 | 12 | 12-001 | 2 | visit 1 | 13.01.2024 | |
| 1 | 12 | 12-002 | 1 | visit 1 | 15.05.2024 | |
| 1 | 13 | 13-001 | 1 | visit 3 | 11.03.2024 | |
| 1 | 13 | 13-001 | 2 | visit 3 | 12.03.2024 | |
| 1 | 13 | 13-002 | 1 | visit 1 | 14.09.2023 | |
| 3 | 40 | 40-001 | 3 | visit 5 | 03.04.2023 | |
| 3 | 40 | 40-001 | 4 | visit 5 | 06.04.2023 | |
| 3 | 40 | 40-002 | 3 | visit 1 | 01.04.2024 | |
| 3 | 40 | 40-002 | 4 | visit 1 | 04.04.2024 | |
| 3 | 33 | 33-001 | 3 | visit 4 | 11.03.2023 | |
| 3 | 33 | 33-001 | 4 | visit 4 | 12.03.2023 | |
| 3 | 33 | 33-002 | 3 | visit 1 | 14.05.2023 |
Hi Anonymous ,
Try out this example code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZFRDsMgCIavYnxuDQIu2VmaHqDPW3r+IVUzgk0fJKB8/IDbFnNcYkY1K4BGcs7jc3yDRpAgJwRkCULclxmCBgn1iQbmEfQqJUGZqJAa15he5wT0hKBBtDEcmEcmjXGCd02noVI9BjVNhQZSxJPywE8IG6Q2Bq+BeQSNSi0g273S+UYFjUr/F2E61hG6jJuF/5dsZ7EIG8QsmSaInyW0RZeG7D8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id_protocol = _t, id_site = _t, name = _t, id_date_form = _t, visit = _t, date_1 = _t, date_2 = _t]), repBlankNull = Table.ReplaceValue(Source," ",null,Replacer.ReplaceValue,{"date_1", "date_2"}), // Relevant steps from here =====> groupNameAllRows = Table.Group(repBlankNull, {"name"}, {{"data", each _, type table [id_protocol=nullable text, id_site=nullable text, name=nullable text, id_date_form=nullable text, visit=nullable text, date_1=nullable text, date_2=nullable text]}}), fillUpNestedName = Table.TransformColumns(groupNameAllRows, {"data", each Table.FillUp(_, {"date_2"})}), expandNestedData = Table.ExpandTableColumn(fillUpNestedName, "data", {"id_protocol", "id_site", "id_date_form", "visit", "date_1", "date_2"}, {"id_protocol", "id_site", "id_date_form", "visit", "date_1", "date_2"}) in expandNestedDataSummary:
groupNameAllRows = Group the table by [name] using the All Rows operator get nested tables.
fillUpNestedName = Apply the Fill Up transformation to the [name] column to each nested table.
expandNestedData = Reinstate the nested columns back to the table.
Example output:
Pete
1 Reply
Hi Anonymous ,
Try out this example code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZFRDsMgCIavYnxuDQIu2VmaHqDPW3r+IVUzgk0fJKB8/IDbFnNcYkY1K4BGcs7jc3yDRpAgJwRkCULclxmCBgn1iQbmEfQqJUGZqJAa15he5wT0hKBBtDEcmEcmjXGCd02noVI9BjVNhQZSxJPywE8IG6Q2Bq+BeQSNSi0g273S+UYFjUr/F2E61hG6jJuF/5dsZ7EIG8QsmSaInyW0RZeG7D8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id_protocol = _t, id_site = _t, name = _t, id_date_form = _t, visit = _t, date_1 = _t, date_2 = _t]), repBlankNull = Table.ReplaceValue(Source," ",null,Replacer.ReplaceValue,{"date_1", "date_2"}), // Relevant steps from here =====> groupNameAllRows = Table.Group(repBlankNull, {"name"}, {{"data", each _, type table [id_protocol=nullable text, id_site=nullable text, name=nullable text, id_date_form=nullable text, visit=nullable text, date_1=nullable text, date_2=nullable text]}}), fillUpNestedName = Table.TransformColumns(groupNameAllRows, {"data", each Table.FillUp(_, {"date_2"})}), expandNestedData = Table.ExpandTableColumn(fillUpNestedName, "data", {"id_protocol", "id_site", "id_date_form", "visit", "date_1", "date_2"}, {"id_protocol", "id_site", "id_date_form", "visit", "date_1", "date_2"}) in expandNestedDataSummary:
groupNameAllRows = Group the table by [name] using the All Rows operator get nested tables.
fillUpNestedName = Apply the Fill Up transformation to the [name] column to each nested table.
expandNestedData = Reinstate the nested columns back to the table.
Example output:
Pete