Forum Discussion
Merge with Hierarchy
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...
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.