Power Query Editor help needed - advanced fill up/down

marieke · April 20, 2021, 8:34am

Hi,

I’m working on a sales overview. For some order_id there are 2 product types, Simple and Confirgurable. For further reports, we only want to keep the “simple” products. But the issue is, that for those order-ID’s the order amont (row_invoiced) is only filled in on the configurable line, but we do need the row_invoiced for future reports.
So this should be filled in also in the simple product line.

So for the orders with both product types, i tried to use fill down/up.
I tried to use ‘buffer’ to make sure the table is sorted correctly, but this is not working properly.
Also, there are some lines that are really null and these should not be filled.

So it should only be filled when order_id AND sku are the same.

I have attached a small Excel file with my remarks and also attached a dummy Power BI file with my attempts so far.

It would be great if somebody can advise me on this case
Sales Item Dummy.pbix (13.4 MB)
Sales Item dummy example.xlsx (18.0 KB)

Melissa · April 20, 2021, 9:36am

Hi @marieke,

I’ve greated two queries, the first is a supporting query for which you can disable load.
This query is called LookUp

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xVvNUtxIEn6VWk6zEdNjlaTSz9FgjHfBhjBt74Rn56Cmi0ZutdSjH4M57TvsZV9hXmBPc/Ob7JNsVpZSqNQlujGD7QiDsKE+ZVXml19mJb/8sufz0An43o97bug7ngsPngcfqnS1ziQ8BL4vIvgs3CiGTxw/TPjEdbj657/ltbxhJ81lPZN1zd5ldZmws6yprtj//vVvdi6vSpY1N02ZfvlvJSv2olivZaa+V62z9+uP7Ru49AYKXER+/xVE4MfemzP1akLBR+YrnK5nWZJUsyQp2e21XM2SOftn4zhueHohk5y9lfJSf21C+gSpHqJAGJBxgHvh+qHF6HO5TsqkBnPe6qemrGTNLr/8UXZbIXMTzeujfZctFvQG6oEHTmAYHHKH4x7H7uYWHyRlxQq9z+0Wm2sHtDY+uJEYrh3i2tyLNhffT2afu4M7b3K1lUdZca1WukMICUE9eK5veIjrOVyoPfXiWFg8pKmvZZaxgyL/rZFlUbLjpqpkzvazpLk2LYkIRz143DcsCXzuO+geNhh9UIdq5TpNMnCOqmYTNr2Wci3LqijyCg7q4grOcbB9MYHi0Ys4NEAjEemj8dxR1JOiyReAV8isdf7D1TotJTsqC5lb3D90Wkw0iLuesaF+5AYOYvo+38T8e7NaT4pJJnPJqnWZ5oslbqiJQKyiV8AQvzMqDPENglGLDorVZVHWrafTvsF+1r39NAGJRHz1wJ3YMAmiDFcPoh0h5Y0KtKuiWOyGThHue4juGfEVRIGjcINxx7lK8vm6WK0BZJXO5zJblEVRs1UBjyYSMZf/vbgkJC7B8ODCj/vvcHIymT6LI0dMpuInF33IYvSJnIP/VLJk6rvYbbLMktXahCFa8YM/nbJCIhTN8CPOIgLMjfzP9xbiGT/a6i1W9N29hcgFD+lbR0VENCOc74FOjCA0BUV98DCKdVb2kak2kvw6yXPl+TUA6dx+mOa3wK8UPnv9BBV5JtTTyomI99EgTQ1JAKmBc891R1ngrExXstvfSWtdZjWOGEdoK8xDjECgYRxGmvpsYJppdJZiOk2twMZ1IS8VQdwhCQMpNO0KAhfdyLOExUkBGYiBEpi8L7KayUwu6zKtLq4kBEVdlCpYzD0kBhDePVCcB/iCj8QK+lieY3qigHjnh+8UrMCI921beJ18kv08b+5b1AcACcEHVBnrdO55jsUem4RWxzNL8uVHaVpCdCJQNkdmVod41oSPkWX3OzOiDzP5CXx/jvnnqriCKNCOuJF04o5KkCzEYAtFFOEWRpYSoScxc23ZbVp/lOzLf1gYsIuVidPFVrC3Lb2Ou/rusiV2+3j3M4fNDUtNGqlcpjX7VBRlhzofsEbccRSKaD5IOnHga9PHNUpnz0wqby9XVwUcmJS53bIumNE79fJ3/BvHqAnzJlO+HJh4Z9Mp2y9T+Unms6Zit0U+B6lQZZDjFkrSmkC+ARTill4U+WW6aMpkZsBBBLjb4MzFu9jFIw+iIfuFQhc3GFnWbeviCwT5eXGZmOsTDwXo4IOICiKuI9cVwTiXH6e4PQegoECM9zNUD4coAiWNHxmOrcQU4EzK4lZ9afOA96ksl0muWGH2uWWJyVv9/T0U4oe2pOdGLeM68EcVG+pffWsVdVBkcgVl++vkJu24Yh9c7kXR6JPswOCVCQxplZsOBsGKvAuqA+lxLA3K1XNVKXWqGBMvGJiyM+XVuelsIDoJFOU3ntrdPgLz8smsrSstVKRPat6ky1mZZqwGf47YRyDeCRWj7h0SMQNywliWJ436dUm+ZxcFUaB5yND0Sizp4hT/d0BEr4AFsmQJFTXyT72t4gW928eKnY0s0jfMZtdIEpnsgk2shD2FeJBHoshxD991hxFZYvpYhdhxUWRtNp5stBE0FSAGthGEaWBbIHF/MkVDQx+7GnyoMu4qJO7bCiTudNShzsYbFPGqK4J6yhcYGJ65fNcVUaRR5OD/N+yoBC4Hlk2SNeCZWEQfoZZgA5oKUZL6qKLsQj5Lqiq9AJguJRqdEZWVreKJc4py3VGIAyPKX5y/m5yD0j863OtyiTukFHSWtTqyD0Bcn1Rj5kiahRKwhAmD6XiQRAww4LHgfjAd7LctpGkUsQh2Lb5Jz4dz4hM8JR4K4wzdSJdhfuxbJKKGrIosKVdJjd7ULUvBjI2J0PQMCGIsPP1NK6Ygl0+U52FAkTz7CIICatjmxqR6TlGrmwK+a4Zt7OhSyC5eyNW5+/4ZRMV7Vqw+FeVKliYEBW2o5ZE/ZCXM/HHMR6ucLpdMr9OPlzJTetrKQZx8LdLBNKg9QizavcjSehsJJiN6ILRGJBl3ye1QZng82CjlAnS7wB3Vt2cQY5OiqVHJANS7tAZmWqO4KUjo5CZ5uOR5kW6OfdtuCwQqwWOO8zZEPfqs73k79MzO0iwrrnVz9wX6KdOKQaa5bBWDCU55ta1t7ylphy2yB5aZ3KUgaevNp4SiYMEy+onUJHcpwaGAe+KuCnejPhpk06GcRK2nM91jO6tbRKbbEQRqSHvzXHjheElgBox6B0jwUEntKnM9yrqxdhqTJ0IswrlF4JoE9aK4zj9DaH6QIGPKJl3tINA84qi2En9CL/aIl+LvdPOpqR/f4Lswo+f34Z+w+aDPC5FQ23OjvBCRH7VlbmxRCqdGH78VO89/a5IybVYWoeMROen++bftOnOPSAs7B9tyrBX/a3KsR+yFUvKbBiyRVRzt4MM7sBVtuOoG2noZ+hYWEdVqQgxs1Xfd46c7sNWoQyYMuCOT83Rxp67OZf0XE7+lKN2B2FIrb79Y0NwwsZvqEhRH+jOgQLMH0dG5+gHuY9ayyvYP4ErXkAvL6nNVS7liywzUignj9WGgNjD8xxV+6GD5EKKWGqB80IF5qzzWFWy5MJf2DQtMPndBuTvEvdaXz5BoLtMM3v8iKYHQ5wupLWA/PP+rCSUISr3rSF9h57h/UFvBDwgad3JEPMS2q9B7+sZK1wPxXM5nwK0DwJAAcdvuqyHseaONguyhNYRwCDjcG71Va4vj+xtFX37/8nt+/7YK3gfb1gOzcumwBSa3QXYRF+mNVZ8GjQA4ztAFhbtfLCssI0XL+rY3SJpLoOy2/btOZbZuIN5NTG+AaYT5Bhr1OaxwRa/bvC7WlvaU6MIEfURnLOMioL0Y7XLGBsj5xVVT3159+QO4sk9imV1aC7+POKIzfEwcXy3k7xqngmJRt46ePhYFxaJu62wFdB8NGBHgbrMuG4CPUjgiJnRvbxfZvIH+aNkcOP032F4Yuo8qDGlAkmP+2H712EN7yNUjpzFI3e3acrm6/VR7+UtuIz0aUNRdsF1E6wb414hWmsLkeE80IuSc7s5h0+BHSzmai+Rct+pD41L09ekkcDCjeyEeime+xesmnxfsZZJBfrD3zYOOi5Br9ZVUv0fvtvoHxcpgeWpcviylvE4+s/M0XwDf2oGIg7A+BkOiTUMUhNBNOKshp/nnm5HViXDcduLE2hxt85I96HfujtI8pG4bejw02PTwvXAO9n9+xsNnGHW8d4/Yw5wenpw/Z8IB1wOkOXohFZBqeKqqi8vbJl0MmsA0F6l7hluqqK3u+JAqiuYldcOQx7Gpkz1Hn1tsAe9f5cgSb3L0ban9LGlQsu0XxsIQG90dx1v9Gpa9tV+oDK/lOY1E6mbh1iuj0d18+JURjUjqBiKPwnBjL9UrBVbreuPCddHo7TxryvXodhKDYP/QcwPj4Pb3j5+/Dd9408l+6ExCrYqj2HaMb4oVOOdxUhYFmyUXyzX8ZaHDTtiLpFyy/awxRSPNR7bdwqdXHTS1qFt0O923j7PAro1IGmDU/jM+f+EhiNVOy/zF3fUVjSjq9bexjWs9uQeRzZ1OpflEDR3wQW3vKtG/h6Mro0dIKMdYD78sE6h1wL3ZDXPxYyiGIoNmB1t7hWO4K91NB5Mpbw9zE7p/Mx1Yb6ZpHFKXpdunoTfs2nW+ldNsom5lPiVt0mxiO364wyDQLo7YW5/CWXPEn78+CQRdqnreMIVj4vGF7idYD6VfIJhrd8yAD/denp9iYrm7PB9JLPdcnkfxAM2QOgZObyJgWwI7LZPcnHbkcccNiBg4xrDPFLzfnbw6UO5l4Z5X6eIqS3AwawrMfpVcL0GSpzVQ38/s1evpAfPFCaiDxdDRaAKR2pxbhmD7gfnQCxAaPtRU/q3rSBpI1GoyNsFVZxAFgqCR2H4Rt07YS2wJVuwHtT6r6mZZmd1AGgvUTSPPNXtHaiAQFf3dQGAP4KhMP96qthGUMjPrYCCnwUDdddzh1xP6Dnj/rydwGtbj7a8+4AEM4glYNIwmJLv0VPYw574BCSHzZYq/7oSJVoeVCRb2wb5HOU9Tg525g2mYvqEjNcaYpfBzjSEoXKcLL91S5EbV6esSQOfmgVP8I60heheyTqCszJkziZkrVitzdcdYPXKMpO75DvakIpTEg+XPk0t5VgATeX7P5yDFJ2W7Xb/+Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [item_id = _t, order_id = _t, product_id = _t, product_type = _t, sku = _t, row_invoiced = _t, store_id = _t, created_at = _t, name = _t, qty_invoiced = _t]),
    ReplaceNull = Table.ReplaceValue(Source,"null",null,Replacer.ReplaceValue,{"row_invoiced"}),
    #"Changed Type" = Table.TransformColumnTypes(ReplaceNull,{{"item_id", Int64.Type}, {"order_id", Int64.Type}, {"product_id", Int64.Type}, {"store_id", Int64.Type}, {"row_invoiced", Currency.Type}, {"created_at", type date}, {"qty_invoiced", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"order_id", "sku", "row_invoiced"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([row_invoiced] <> null)),
    #"Removed Duplicates" = Table.Buffer( Table.Distinct(#"Filtered Rows"))
in
    #"Removed Duplicates"

.
For the second query I’ve written a custom function fxUpdatePrice and incorporated that into the code. You can copy both these queries into a new file, new blank query (make sure you rename the first query BEFORE you add the second)

let
    fxUpdatePrice = (cPrice, cOrder, cSKU) => if cPrice = null then try Table.SelectRows( LookUp, (IT)=> IT[order_id] = cOrder and IT[sku] = cSKU ){0}[row_invoiced] otherwise cPrice else cPrice,

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xVvNUtxIEn6VWk6zEdNjlaTSz9FgjHfBhjBt74Rn56Cmi0ZutdSjH4M57TvsZV9hXmBPc/Ob7JNsVpZSqNQlujGD7QiDsKE+ZVXml19mJb/8sufz0An43o97bug7ngsPngcfqnS1ziQ8BL4vIvgs3CiGTxw/TPjEdbj657/ltbxhJ81lPZN1zd5ldZmws6yprtj//vVvdi6vSpY1N02ZfvlvJSv2olivZaa+V62z9+uP7Ru49AYKXER+/xVE4MfemzP1akLBR+YrnK5nWZJUsyQp2e21XM2SOftn4zhueHohk5y9lfJSf21C+gSpHqJAGJBxgHvh+qHF6HO5TsqkBnPe6qemrGTNLr/8UXZbIXMTzeujfZctFvQG6oEHTmAYHHKH4x7H7uYWHyRlxQq9z+0Wm2sHtDY+uJEYrh3i2tyLNhffT2afu4M7b3K1lUdZca1WukMICUE9eK5veIjrOVyoPfXiWFg8pKmvZZaxgyL/rZFlUbLjpqpkzvazpLk2LYkIRz143DcsCXzuO+geNhh9UIdq5TpNMnCOqmYTNr2Wci3LqijyCg7q4grOcbB9MYHi0Ys4NEAjEemj8dxR1JOiyReAV8isdf7D1TotJTsqC5lb3D90Wkw0iLuesaF+5AYOYvo+38T8e7NaT4pJJnPJqnWZ5oslbqiJQKyiV8AQvzMqDPENglGLDorVZVHWrafTvsF+1r39NAGJRHz1wJ3YMAmiDFcPoh0h5Y0KtKuiWOyGThHue4juGfEVRIGjcINxx7lK8vm6WK0BZJXO5zJblEVRs1UBjyYSMZf/vbgkJC7B8ODCj/vvcHIymT6LI0dMpuInF33IYvSJnIP/VLJk6rvYbbLMktXahCFa8YM/nbJCIhTN8CPOIgLMjfzP9xbiGT/a6i1W9N29hcgFD+lbR0VENCOc74FOjCA0BUV98DCKdVb2kak2kvw6yXPl+TUA6dx+mOa3wK8UPnv9BBV5JtTTyomI99EgTQ1JAKmBc891R1ngrExXstvfSWtdZjWOGEdoK8xDjECgYRxGmvpsYJppdJZiOk2twMZ1IS8VQdwhCQMpNO0KAhfdyLOExUkBGYiBEpi8L7KayUwu6zKtLq4kBEVdlCpYzD0kBhDePVCcB/iCj8QK+lieY3qigHjnh+8UrMCI921beJ18kv08b+5b1AcACcEHVBnrdO55jsUem4RWxzNL8uVHaVpCdCJQNkdmVod41oSPkWX3OzOiDzP5CXx/jvnnqriCKNCOuJF04o5KkCzEYAtFFOEWRpYSoScxc23ZbVp/lOzLf1gYsIuVidPFVrC3Lb2Ou/rusiV2+3j3M4fNDUtNGqlcpjX7VBRlhzofsEbccRSKaD5IOnHga9PHNUpnz0wqby9XVwUcmJS53bIumNE79fJ3/BvHqAnzJlO+HJh4Z9Mp2y9T+Unms6Zit0U+B6lQZZDjFkrSmkC+ARTill4U+WW6aMpkZsBBBLjb4MzFu9jFIw+iIfuFQhc3GFnWbeviCwT5eXGZmOsTDwXo4IOICiKuI9cVwTiXH6e4PQegoECM9zNUD4coAiWNHxmOrcQU4EzK4lZ9afOA96ksl0muWGH2uWWJyVv9/T0U4oe2pOdGLeM68EcVG+pffWsVdVBkcgVl++vkJu24Yh9c7kXR6JPswOCVCQxplZsOBsGKvAuqA+lxLA3K1XNVKXWqGBMvGJiyM+XVuelsIDoJFOU3ntrdPgLz8smsrSstVKRPat6ky1mZZqwGf47YRyDeCRWj7h0SMQNywliWJ436dUm+ZxcFUaB5yND0Sizp4hT/d0BEr4AFsmQJFTXyT72t4gW928eKnY0s0jfMZtdIEpnsgk2shD2FeJBHoshxD991hxFZYvpYhdhxUWRtNp5stBE0FSAGthGEaWBbIHF/MkVDQx+7GnyoMu4qJO7bCiTudNShzsYbFPGqK4J6yhcYGJ65fNcVUaRR5OD/N+yoBC4Hlk2SNeCZWEQfoZZgA5oKUZL6qKLsQj5Lqiq9AJguJRqdEZWVreKJc4py3VGIAyPKX5y/m5yD0j863OtyiTukFHSWtTqyD0Bcn1Rj5kiahRKwhAmD6XiQRAww4LHgfjAd7LctpGkUsQh2Lb5Jz4dz4hM8JR4K4wzdSJdhfuxbJKKGrIosKVdJjd7ULUvBjI2J0PQMCGIsPP1NK6Ygl0+U52FAkTz7CIICatjmxqR6TlGrmwK+a4Zt7OhSyC5eyNW5+/4ZRMV7Vqw+FeVKliYEBW2o5ZE/ZCXM/HHMR6ucLpdMr9OPlzJTetrKQZx8LdLBNKg9QizavcjSehsJJiN6ILRGJBl3ye1QZng82CjlAnS7wB3Vt2cQY5OiqVHJANS7tAZmWqO4KUjo5CZ5uOR5kW6OfdtuCwQqwWOO8zZEPfqs73k79MzO0iwrrnVz9wX6KdOKQaa5bBWDCU55ta1t7ylphy2yB5aZ3KUgaevNp4SiYMEy+onUJHcpwaGAe+KuCnejPhpk06GcRK2nM91jO6tbRKbbEQRqSHvzXHjheElgBox6B0jwUEntKnM9yrqxdhqTJ0IswrlF4JoE9aK4zj9DaH6QIGPKJl3tINA84qi2En9CL/aIl+LvdPOpqR/f4Lswo+f34Z+w+aDPC5FQ23OjvBCRH7VlbmxRCqdGH78VO89/a5IybVYWoeMROen++bftOnOPSAs7B9tyrBX/a3KsR+yFUvKbBiyRVRzt4MM7sBVtuOoG2noZ+hYWEdVqQgxs1Xfd46c7sNWoQyYMuCOT83Rxp67OZf0XE7+lKN2B2FIrb79Y0NwwsZvqEhRH+jOgQLMH0dG5+gHuY9ayyvYP4ErXkAvL6nNVS7liywzUignj9WGgNjD8xxV+6GD5EKKWGqB80IF5qzzWFWy5MJf2DQtMPndBuTvEvdaXz5BoLtMM3v8iKYHQ5wupLWA/PP+rCSUISr3rSF9h57h/UFvBDwgad3JEPMS2q9B7+sZK1wPxXM5nwK0DwJAAcdvuqyHseaONguyhNYRwCDjcG71Va4vj+xtFX37/8nt+/7YK3gfb1gOzcumwBSa3QXYRF+mNVZ8GjQA4ztAFhbtfLCssI0XL+rY3SJpLoOy2/btOZbZuIN5NTG+AaYT5Bhr1OaxwRa/bvC7WlvaU6MIEfURnLOMioL0Y7XLGBsj5xVVT3159+QO4sk9imV1aC7+POKIzfEwcXy3k7xqngmJRt46ePhYFxaJu62wFdB8NGBHgbrMuG4CPUjgiJnRvbxfZvIH+aNkcOP032F4Yuo8qDGlAkmP+2H712EN7yNUjpzFI3e3acrm6/VR7+UtuIz0aUNRdsF1E6wb414hWmsLkeE80IuSc7s5h0+BHSzmai+Rct+pD41L09ekkcDCjeyEeime+xesmnxfsZZJBfrD3zYOOi5Br9ZVUv0fvtvoHxcpgeWpcviylvE4+s/M0XwDf2oGIg7A+BkOiTUMUhNBNOKshp/nnm5HViXDcduLE2hxt85I96HfujtI8pG4bejw02PTwvXAO9n9+xsNnGHW8d4/Yw5wenpw/Z8IB1wOkOXohFZBqeKqqi8vbJl0MmsA0F6l7hluqqK3u+JAqiuYldcOQx7Gpkz1Hn1tsAe9f5cgSb3L0ban9LGlQsu0XxsIQG90dx1v9Gpa9tV+oDK/lOY1E6mbh1iuj0d18+JURjUjqBiKPwnBjL9UrBVbreuPCddHo7TxryvXodhKDYP/QcwPj4Pb3j5+/Dd9408l+6ExCrYqj2HaMb4oVOOdxUhYFmyUXyzX8ZaHDTtiLpFyy/awxRSPNR7bdwqdXHTS1qFt0O923j7PAro1IGmDU/jM+f+EhiNVOy/zF3fUVjSjq9bexjWs9uQeRzZ1OpflEDR3wQW3vKtG/h6Mro0dIKMdYD78sE6h1wL3ZDXPxYyiGIoNmB1t7hWO4K91NB5Mpbw9zE7p/Mx1Yb6ZpHFKXpdunoTfs2nW+ldNsom5lPiVt0mxiO364wyDQLo7YW5/CWXPEn78+CQRdqnreMIVj4vGF7idYD6VfIJhrd8yAD/denp9iYrm7PB9JLPdcnkfxAM2QOgZObyJgWwI7LZPcnHbkcccNiBg4xrDPFLzfnbw6UO5l4Z5X6eIqS3AwawrMfpVcL0GSpzVQ38/s1evpAfPFCaiDxdDRaAKR2pxbhmD7gfnQCxAaPtRU/q3rSBpI1GoyNsFVZxAFgqCR2H4Rt07YS2wJVuwHtT6r6mZZmd1AGgvUTSPPNXtHaiAQFf3dQGAP4KhMP96qthGUMjPrYCCnwUDdddzh1xP6Dnj/rydwGtbj7a8+4AEM4glYNIwmJLv0VPYw574BCSHzZYq/7oSJVoeVCRb2wb5HOU9Tg525g2mYvqEjNcaYpfBzjSEoXKcLL91S5EbV6esSQOfmgVP8I60heheyTqCszJkziZkrVitzdcdYPXKMpO75DvakIpTEg+XPk0t5VgATeX7P5yDFJ2W7Xb/+Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [item_id = _t, order_id = _t, product_id = _t, product_type = _t, sku = _t, row_invoiced = _t, store_id = _t, created_at = _t, name = _t, qty_invoiced = _t]),
    ReplaceNull = Table.ReplaceValue(Source,"null",null,Replacer.ReplaceValue,{"row_invoiced"}),
    ChType = Table.TransformColumnTypes(ReplaceNull,{{"item_id", Int64.Type}, {"order_id", Int64.Type}, {"product_id", Int64.Type}, {"store_id", Int64.Type}, {"row_invoiced", Currency.Type}, {"created_at", type date}, {"qty_invoiced", Int64.Type}}),
    UpdatePrice = Table.RenameColumns( Table.RemoveColumns( Table.AddColumn(ChType, "Temp", each fxUpdatePrice([row_invoiced], [order_id], [sku]), type number ), "row_invoiced" ), {"Temp", "row_invoiced"})
in
    UpdatePrice

.
I hope this is helpful.

marieke · April 20, 2021, 10:54am

Hi @Melissa,

this is exactly what I wanted!

Many thanks for helping me out!