Merge with Hierarchy

[irfan_abdrhman]

Hello Community,

Here is the Source Data, where Merged Data is my data in power bi that has been merged, and the expected Outcome

According to the merged data, it has merged the queries line by line by duplicating the queries in the first to match with the second query. However, I want it to have a mapping from query one to query two without merging them into a single line and then have duplicated query one for each item in query two. Query two is the columns that begin with "Expense Item." As you can see in the expected Outcome, it has shown that query one is in a different line item while query two is underneath respective parent item, however the "Name" column is the combination of "Name" and "Expense Item.Name". Please refer to the excel sample in Source Data, and please let me know if there's any solution, thank you all.

[Anonymous]

Hi @irfan_abdrhman 

You can create a query like the following.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZrbcptMEoBfZcpXdtU/mGE4+mqRhA6ODgRQnK0kFxjhmAoCLUKxvU+y77Mvtt2DwLLjpH6McrNxFSXjFoLpb3r6NHz6dMJP/jpZlkmalEm8Jb0kTQkls7CIbokiK/gtHNOP7y6Xqha8f//RVK8+vH9ZBMc4IHlWFmFU5gXxVxnp3a5ArKmyXF0QbjZF/j1G4SQr4yIL0+oLOIxzzs7xoUS/0MwL2SD2DMSMnTNeyc0LZlyoViXn51yuxOqFLMNB3Fl1o59/87PDWOnXKz2+ppqhM6qaq4iaq9CkZqRGFjfUWI9NHIqlqpHJFcqi65iq0TWnZhhyylcRU2X92mQmXsYlBp9OGkdlkURJ+QD/JWW8RhKyIMHq0y9/vU3B75kCBT6vQtDvET5v2PM39L8RPXIL4vUmD4sH4gN8MgyjPeTH2WDNbLD9bOC5uNwNH4p8PxcPpPU8HLC1lJr9qghvyl+DNy5k/YLrP4A3Lhi/kNVKnu3S/W8fz359oFqjOIuLMCXT8Bpso1/EdyC8+wYfSmWVaj1YBOFSWWYo9QPHmVJnRGXJ+qhI7CNricLpL+ZDz545ZGZ775xgMh8RfzAnvfEAR6Zo6lM6bpFHuyJex1n5OP5nJqi8aJpwvJqQuwATgMHIGupcxnFKfHCeSZ7RyovmERmF6fcwS7YxqS4Y5AByWITrmJwySV6vSXn77YwSS8bze6IwPIH7zRfwoevCgGuda8gyioNkTf2AzsZXlN+rLQH7S9fxiL+cexPfOUBryM8Nrwva2hu8Gq2q7DW9jgvqh3cZKcU5LLF5XqzBMmfJPRmHxYpc5fmKnAINgMk/nwBK9TPeKVjMK73Eiq1VbEji1LlJGtMiovF9ScGs18p926U7G/SJ6zl92w/IZD5Y+oE3cXzkKiFYCReMaqrHRKt3QitbJlNq1b0+8f+1CwvwYPgv35ui+Ev5mjj3JQwRzBr1HwVj1EY2hFHKjWbCE8KcgWBxc5NEYPC7zSatfGcbls9+TTyy3Iq5+8E0X+ETBVONdfGJbpHgc3FMcShQiHGhyei1af3fg0DbmSXbqIagPiaI6pP1hY6pCgfDhTcLJg7t0WHQkgT4qdmCOPPA8dxnDovpR3VYXWMBM5BM5epnmMYlYbqlfhTe3OTpKsm+kiH4LcgoKP69y4tvxH/YQmZBeiR42FRiEiTx6WIHvybLLCnP8PZ9H4cr6XsDqDVvwi6a33S49Kjvk54z749pz/Yn85agnQ+O548ncwe82eLS6QcHpBV2TNLM7ERaMQ0LTWua5xBah7sCOMFQqF+GSZbG2+0+3F4h4V6cQWZ8l5S35Crc3pJeuE3Ql80X/qNerD7dr18xj0+zustdFndK65h+tLxO+V15nabVeR2TlT8AByrWz7OoiEE5wQMEt/ih49gEDS7/ASD4IYhUGAYVCQHph5vtEygKZk1M+wOgqC9BGRX5LluBWwnXv+Ii4h+WllfgTBdDOnD7tC0Pex7YEzK2vcGV7T2Je4YsAXflKaBOHlnpWgcxoSxGvU2R5zcQ7VBnMovX10UI1vAPwslNCY54Vd7ClfgwRVI4OiRDF52PRquGH6aZ9nQ5oz5Eo8liTmfDlggxpkFuDnER7zOZT5azZzn6HinnHEZh8OMhlbsFOaZrlf7pbp1kyW7dlJePEmz+xCVJsijdifwCkvVit4WLSD/f4hfEtoku65wGGsH2BmQVM+wYDeEGW5yHdyMcuiz6H1zhksUOYTQzgdL3UCUUD7QPT7iNC2+XUaXlbEztoe2NHGKPRp4zsoNnM6B3SDHMcxOlKrEuVOOCdzNnWdNMpdGYwDhWu6jc1qoT0B08gQKF5qkGZRLmaFWlqYjWkNBDqNPww7sd1OzK0Wp2YSmSahzRF8hyN3imyYyTVxTuyg+Fu65Uua7ENE1/omyT+mriSVC4jtLw3/EK69d7XoeOv+9p/+m/2GxSNV0ytOOhZVY3n8C56GKgwmCNkMWtk4hUmgPcJ7X7yWPdoGpCFQSqSRCm3uC1hSdWtGm8kWtNDsLYCTaMrUPDk0W6KXShC7ffklrgeEvkdrlsQrk9fTFNOm57gHeDKJsiRarVLiBg416DmxdlGmYIshd+JdXXGFfIt6/nIMHg0rNFlN67wFqzxhiVLjSdGaREfdudBABxz458EtLAs+e+u/AC8nkHIUxHwUBYau/LicgSjtkT0DraqGppHfFKla1WHT1UrrFXbML3bc91AhpMpo7fkvFwulh4o+Vk8MQ6FZN1yXh+ANgtbCuaqQmAYbGJSyrWOtH2S1t7srQVRRLFjmaI8e8VacxRbIkFUzqc9+cjOh6M/LHv+m3bfs58FDgfydUElvSLGTuTVcuSLFNkBsdi2K2xrmiySH2etwCHcRZhQTTOSzJINhtwm83W0IoAHjDTLY5W7JJysEMudiXFyj7Qs0GM2aMbFiU443WPqvcmZW1zcX/pTYQPPSCqasoRDVLpaJC6qVt7PRMsfEgvh5RxWzsjO4riLYaj9TXKYYEzhVnVPprKzfUaBdUWm9SYrthTU6q0Umj7FtFbR3ThJBmq8wavLTwRMVUuHbDTm1t9SLIH7J65ELC+tWSHDX/wjkvXc2aHUUY3Yap0lR+xPnx9Q61a1waTf67zDAeKLtAULpCpimTg1aopm5JuWfwN3N8Cx6u3iRhjpsQPm2pv2H6FTdElkU+YlgpasIOKD+P6vpd75dM+dT/0ldYerr/w3l0uJvOAnM7sKfi7iX126Oh07Zjxt2NGbcnWjz1dUJsIybbMN0QRvu50iPtIebYti51oUZLLPMnKLebWYmCaVPk+udaxoYoPcNMHOr9yqbGBE8hl7lvnMoEzWo7JMrBn9mON8thYNI5ZBirdXmtRNM20KqXv8nyFihN6ahDQ/Yw2+Yrog2GaY8CZWZ2d1KGEi7cvqirFOKwDxX2TDURhSC5pj5ptzXPmjGxiT6cTe953SG85mQ5E09F1p8+75VXL/lhMO+4HMy5eFWp03yfWkwJMEYWw3vtpuIXU8aQnmoz6mqRx9rW8pcQEC/7vf06al1uYbopXYZVGzZqvyBrdIo6+l/RudUf11yU5Nd6XXxmqU3HTZEesa5SOGY9pGbXy4bYk/V36PYbqo/6/l9/XMnJ3npMVFN53cVhir/wmzQUqsOx6B49c4SZQ9arR6fgM5gNfhjudnlXb9EyV62XQz+GexE/Da3i67wRCNaWapYZSMz+stoFBvruGZHQ8cB3KtN+2ChgsZcmyjvg6In/1OznVPEEprtUQUHsAWBS7r+CvV/VCqNiADxcbtQz3L8gqCbFEEvsoksk5F2zlQxUbyEh/+rWk7+J0FxZ06gyovytuWjK+XM56i7E9fzHfP9I2kNm8eNwG5/6Kln++fPkf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, Name = _t, description = _t, creator_id = _t, changed_by = _t, supplier_id = _t, #"Supplier Name" = _t, Amount = _t, paid_amount = _t, Status = _t, Type = _t, payment_type = _t, payment_reference = _t, created_at = _t, updated_at = _t, issued_at = _t, received_at = _t, approved_at = _t, paid_at = _t, reference_number = _t, integration_state = _t, integration_state_changed_by = _t, integration_state_changed_at = _t, external_id = _t, external_system = _t, message = _t, last_sync_time = _t, #"Expense Items.budget_id" = _t, #"Expense Items.contract_id" = _t, #"Expense Items.Number" = _t, #"Expense Items.Name" = _t, #"Expense Items.Unit" = _t, #"Expense Items.Unit Price" = _t, #"Expense Items.Quantity" = _t, #"Expense Items.Amount" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Number"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1), type table}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Name", "description", "creator_id", "changed_by", "supplier_id", "Supplier Name", "Amount", "paid_amount", "Status", "Type", "payment_type", "payment_reference", "created_at", "updated_at", "issued_at", "received_at", "approved_at", "paid_at", "reference_number", "integration_state", "integration_state_changed_by", "integration_state_changed_at", "external_id", "external_system", "message", "last_sync_time", "Expense Items.budget_id", "Expense Items.contract_id", "Expense Items.Number", "Expense Items.Name", "Expense Items.Unit", "Expense Items.Unit Price", "Expense Items.Quantity", "Expense Items.Amount", "Index"}, {"Name", "description", "creator_id", "changed_by", "supplier_id", "Supplier Name", "Amount", "paid_amount", "Status", "Type", "payment_type", "payment_reference", "created_at", "updated_at", "issued_at", "received_at", "approved_at", "paid_at", "reference_number", "integration_state", "integration_state_changed_by", "integration_state_changed_at", "external_id", "external_system", "message", "last_sync_time", "Expense Items.budget_id", "Expense Items.contract_id", "Expense Items.Number", "Expense Items.Name", "Expense Items.Unit", "Expense Items.Unit Price", "Expense Items.Quantity", "Expense Items.Amount", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "Custom", each if [Index]=1 then List.Numbers(1,2,1) else null),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Custom",each [Number],each if [Custom]=1 then [Number] else null,Replacer.ReplaceValue,{"Number"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [Expense Items.Number],each if [Custom]=1 then null else [Expense Items.Number],Replacer.ReplaceValue,{"Expense Items.Number"})
in
    #"Replaced Value1"

Best Regards!

Yolo Zhu

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