Forum Discussion
Single product on document
Hi,
I have a problem with power query that I can't deal with, namely: I have many columns (2 are the most important)
1. Document - containing the document numbers and how many items there are (the document is nothing more than the receipt number, as in MCdonalds),
2. ProductName - here are the names of the products appearing on the document,
Now I would like to check how many documents were Coca Cola and what else was on those receipts. Simply analyzing the sale of a product on the basis of verification of what is associated with a given product on a given document.
Unfortunately, I did not find a similar topic here, so I assume the topic:)
Hi Anonymous ,
I've gone about this a different way from before due to the extra flexibility you need around selecting different items. As you need this to be dynamic, I've done it in DAX instead of Power Query.
I've called your example data 'factSales', which you will see referenced below, which you can replicate with the following PQ code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zVjNbttGEH6VhYDeVIc/oiwdV+Ra2pLcZXbJMmQaGHYNBEUCJ0iQS25G/Qa5FH2VHGO9V2eWS8qm5J+EbNGDLZrWfJyfb2a+5cuXk4yqZ3Pf9YLJdKIzXs58uIBPSSKmM6ZoJDXRdW7vwocLP958upy8mu7Mj+Hm63dnb98sPbiiYCgTojPF83oKNyLFRcytse/fNXaDIcbNky/OLtHxnK45zVmSMFITLTVLSUjVSgqqKEZAdV4RTlIaUyVFZTFn7l1MJxgf0/PHx9zFPjPVo+sNy3NOSoTkjGRM5xIeoIpYJjfXh4Ex5Yvj6eKOs54BvnztLrAgKVM8pEKC2faKnsItQTP5CyNrqW6uQ2bdc+cH3FtL/B0riDCTKY+kkmWFPvFQ5rlxqi6yNkKvV13PMY6cf/qAHoUbxjRbFWrNFBKjueqyc9x7vP/jts6iTcD8TgJqkiYs5hDMI6no4y0N3ltDdR2ruorY9iqmABgXKqyhHERQInWuGkKQ1eo51kvVLL651jv6e/8RsNvMAQ+ZtWFcsJgJ4kwDkpAVC+uY9gZCMKq57wwy94Y93U6zofR3DcrnS6SfljEpq5DrmIoKOwEaffsn1IfMHAdI1XyFQ2vqDY1bSG/Wc8y1jpnIdJVRqC3VmgIfWcLE+ql8dJd23LtOO+4zWiRUMEVWRX70+MQfASEYjIA3Lz6+MQ1+oqocEug5DmYhkhHFP6El5IrL8t5ENJPufdC6sC64EExrSxQSOI6pzi037unBQRjuYgQ/TEJFzd0APSohBWRN62cCfgH7/cRm1DKk5qno0uL0iNa4Ayv5GIuz5gL8YCcMdpYCxhpPaBLLjYQNlsrQAGEq5/7dTeIj+vnl2fvf56fujrM1ZwksO6KZCDdA4STf0EKbbzydv7D53l0YT1P+gmhszpyYwrP4FgVKClOx3IV6aNCNLSDmzeo5MxyHiLngiFdXAoYuNjnDhZjyVJ4KVuKChKxk3TD2euF6jSB5P+/YoXi6YrDBRMsP39/jx95Mnv1/UOwke/vuAi9AagKvEhmBNvjtk+OYAWxuVs/KSh2FBexcJXhYd5OxLxRGxrMzKcAot1+rkrOQ8IxiG923RHoc8N2WV9gX2yup5fZvSBb9NaEvOnKlha5/TrmMpniJbNuomorJgQ09Wx5YcqbdZ9gNGcs0B/+8B7rcO7BlTZfPEav89iWC6m2/yu3VzXUUy5Le2+smYu+AQ0ZwIV0bgUVOFBVh0UTR01zLg/vyh82Xg8yt5gfz+c48k8n3Pfp4Z4sMjGX9hCe3aqNRK2pFc0pAGShhxEGa3K84evKgU634Cac3BfsDznEyy7gk8weR+pvVDsUPf2Ba1vI0lDqVkA6eG3I+eCRzuvOc2xivEimjCoamycEdW6+fStt3/l7fPVl5YjEGioIRIPzBEN7cVhP/mdA8Z48J0P6AWA4EMAe488+f0BEl9bcv26ujut19MKt8q0nsAQ5DnfVPkk47pExJmyGVwgCEQfWQHjkksYbDuME43iwsjGfoito+B0WPBQYpTxY/3QPTE1lG89UxSwzK84JpGvEkoaSEASxLLpDpEYgHCkrh5i/dAe3Nn8ACmWwLGm7gnPcYSNBnCxbbyPHZwnYYaTsw0zXDbSnYrWbcl+eHxtGoiK1K/vDZpCyHSalsoBUe6vd3Zl8mzDoCYM5yCT4QHW5KlmVMm+35HTzwFmOiOc3rEhMZdBTsALYqElzCNcgGEAVx/23J3tR2hiJ4s250m3VsJYHOZRgT3Cc5XzMjCo4mT1UF/wLkYjL4xVIwGMLubSDjskEpGY71GrY+HMUjGKxHBynZ31fBeDAjHv7GgXIbhfnRrMPmhBYjLUHd2IOPvdmF1X/FGQwFaLcohtJpIv9BMWTo9uof", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DokumentTow = _t, IndeksTow = _t, Nazwa = _t, GrupaAdm = _t, Ilosc = _t, Sprzedaz = _t]), repCommaDecimal = Table.ReplaceValue(Source,",",".",Replacer.ReplaceText,{"Sprzedaz"}), chgTypes = Table.TransformColumnTypes(repCommaDecimal,{{"DokumentTow", type text}, {"IndeksTow", type text}, {"Nazwa", type text}, {"GrupaAdm", type text}, {"Ilosc", Int64.Type}, {"Sprzedaz", type number}}) in chgTypesYou can delete the 'repCommaDecimal' step to return to your regional number formats.
1) I set up a disconnected product table (nazwaDisc) in Power Query, like this:
let Source = Table.Distinct(Table.SelectColumns(factSales, "Nazwa")) in Source2) I sent both tables to the data model, ensuring that is NO relationship between them (PBI might do this automatically when loaded, so make sure to delete if it does).
3) I created a basic measure to count items sold:
_noofItemsSold = SUM(factSales[Ilosc])Then I created the following measure that will count all items on all transactions that contain the item selected from the disconnected product table:
_noofItemsSoldWithSelection = CALCULATE( [_noofItemsSold], CALCULATETABLE( VALUES(factSales[DokumentTow]), INTERSECT( ALL(factSales[Nazwa]), VALUES(NazwaDisc[Nazwa]) ) ) )This gives me the following output:
I've attached my PBIX below so you can have a play with it and see how it's all set up.
Pete
16 Replies
Hi Anonymous ,
I've gone about this a different way from before due to the extra flexibility you need around selecting different items. As you need this to be dynamic, I've done it in DAX instead of Power Query.
I've called your example data 'factSales', which you will see referenced below, which you can replicate with the following PQ code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zVjNbttGEH6VhYDeVIc/oiwdV+Ra2pLcZXbJMmQaGHYNBEUCJ0iQS25G/Qa5FH2VHGO9V2eWS8qm5J+EbNGDLZrWfJyfb2a+5cuXk4yqZ3Pf9YLJdKIzXs58uIBPSSKmM6ZoJDXRdW7vwocLP958upy8mu7Mj+Hm63dnb98sPbiiYCgTojPF83oKNyLFRcytse/fNXaDIcbNky/OLtHxnK45zVmSMFITLTVLSUjVSgqqKEZAdV4RTlIaUyVFZTFn7l1MJxgf0/PHx9zFPjPVo+sNy3NOSoTkjGRM5xIeoIpYJjfXh4Ex5Yvj6eKOs54BvnztLrAgKVM8pEKC2faKnsItQTP5CyNrqW6uQ2bdc+cH3FtL/B0riDCTKY+kkmWFPvFQ5rlxqi6yNkKvV13PMY6cf/qAHoUbxjRbFWrNFBKjueqyc9x7vP/jts6iTcD8TgJqkiYs5hDMI6no4y0N3ltDdR2ruorY9iqmABgXKqyhHERQInWuGkKQ1eo51kvVLL651jv6e/8RsNvMAQ+ZtWFcsJgJ4kwDkpAVC+uY9gZCMKq57wwy94Y93U6zofR3DcrnS6SfljEpq5DrmIoKOwEaffsn1IfMHAdI1XyFQ2vqDY1bSG/Wc8y1jpnIdJVRqC3VmgIfWcLE+ql8dJd23LtOO+4zWiRUMEVWRX70+MQfASEYjIA3Lz6+MQ1+oqocEug5DmYhkhHFP6El5IrL8t5ENJPufdC6sC64EExrSxQSOI6pzi037unBQRjuYgQ/TEJFzd0APSohBWRN62cCfgH7/cRm1DKk5qno0uL0iNa4Ayv5GIuz5gL8YCcMdpYCxhpPaBLLjYQNlsrQAGEq5/7dTeIj+vnl2fvf56fujrM1ZwksO6KZCDdA4STf0EKbbzydv7D53l0YT1P+gmhszpyYwrP4FgVKClOx3IV6aNCNLSDmzeo5MxyHiLngiFdXAoYuNjnDhZjyVJ4KVuKChKxk3TD2euF6jSB5P+/YoXi6YrDBRMsP39/jx95Mnv1/UOwke/vuAi9AagKvEhmBNvjtk+OYAWxuVs/KSh2FBexcJXhYd5OxLxRGxrMzKcAot1+rkrOQ8IxiG923RHoc8N2WV9gX2yup5fZvSBb9NaEvOnKlha5/TrmMpniJbNuomorJgQ09Wx5YcqbdZ9gNGcs0B/+8B7rcO7BlTZfPEav89iWC6m2/yu3VzXUUy5Le2+smYu+AQ0ZwIV0bgUVOFBVh0UTR01zLg/vyh82Xg8yt5gfz+c48k8n3Pfp4Z4sMjGX9hCe3aqNRK2pFc0pAGShhxEGa3K84evKgU634Cac3BfsDznEyy7gk8weR+pvVDsUPf2Ba1vI0lDqVkA6eG3I+eCRzuvOc2xivEimjCoamycEdW6+fStt3/l7fPVl5YjEGioIRIPzBEN7cVhP/mdA8Z48J0P6AWA4EMAe488+f0BEl9bcv26ujut19MKt8q0nsAQ5DnfVPkk47pExJmyGVwgCEQfWQHjkksYbDuME43iwsjGfoito+B0WPBQYpTxY/3QPTE1lG89UxSwzK84JpGvEkoaSEASxLLpDpEYgHCkrh5i/dAe3Nn8ACmWwLGm7gnPcYSNBnCxbbyPHZwnYYaTsw0zXDbSnYrWbcl+eHxtGoiK1K/vDZpCyHSalsoBUe6vd3Zl8mzDoCYM5yCT4QHW5KlmVMm+35HTzwFmOiOc3rEhMZdBTsALYqElzCNcgGEAVx/23J3tR2hiJ4s250m3VsJYHOZRgT3Cc5XzMjCo4mT1UF/wLkYjL4xVIwGMLubSDjskEpGY71GrY+HMUjGKxHBynZ31fBeDAjHv7GgXIbhfnRrMPmhBYjLUHd2IOPvdmF1X/FGQwFaLcohtJpIv9BMWTo9uof", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DokumentTow = _t, IndeksTow = _t, Nazwa = _t, GrupaAdm = _t, Ilosc = _t, Sprzedaz = _t]), repCommaDecimal = Table.ReplaceValue(Source,",",".",Replacer.ReplaceText,{"Sprzedaz"}), chgTypes = Table.TransformColumnTypes(repCommaDecimal,{{"DokumentTow", type text}, {"IndeksTow", type text}, {"Nazwa", type text}, {"GrupaAdm", type text}, {"Ilosc", Int64.Type}, {"Sprzedaz", type number}}) in chgTypesYou can delete the 'repCommaDecimal' step to return to your regional number formats.
1) I set up a disconnected product table (nazwaDisc) in Power Query, like this:
let Source = Table.Distinct(Table.SelectColumns(factSales, "Nazwa")) in Source2) I sent both tables to the data model, ensuring that is NO relationship between them (PBI might do this automatically when loaded, so make sure to delete if it does).
3) I created a basic measure to count items sold:
_noofItemsSold = SUM(factSales[Ilosc])Then I created the following measure that will count all items on all transactions that contain the item selected from the disconnected product table:
_noofItemsSoldWithSelection = CALCULATE( [_noofItemsSold], CALCULATETABLE( VALUES(factSales[DokumentTow]), INTERSECT( ALL(factSales[Nazwa]), VALUES(NazwaDisc[Nazwa]) ) ) )This gives me the following output:
I've attached my PBIX below so you can have a play with it and see how it's all set up.
Pete
Hi Anonymous ,
Can you provide an example of what your source columns look like please?
Ta,
Pete
Due to the fact that I can not show more data, I have prepared a trial table that you can operate on. My point is to check selected main items, for example "burger" on which "dokument" appears and with what. Of course, there may be more needs to check "burger" and "frytki", or other combinations, the point is that when we check sales in restaurants, we want to know whether the staff recommends something else for sale or rather passive and the guest chooses from the card himself and they just save the orders without creating any sales
Hi Anonymous ,
I understand what you're trying to do here, and the solution actually needs to be approached in a different way in order to give you the flexibility to change the item that you want to focus on each time (e.g. select burger, or cola, or pizza etc.).
Can you copy the example data you've prepared in Excel and paste it directly into a post here so I can copy it straight out please? I'm afraid I don't have the time to manually type all this information into a new table to work on.
Thanks,
Pete
Ok, so I've made a basic example of the steps to get to a solid basis for your analysis. In the absence of copyable data and a clear indication of the anlysis required, I'm hoping you can take this base and amend it to exactly what you need.
First, copy this and paste it over the default code in Advanced Editor to see the steps in action:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZlNkiUhCITv0uteqKDieo7R0fe/xvAmooXhq9fbDERMfkyrvr4+ZByZ++PzQ/60ph/fn/+gYatCcrRCWzsXNi4c/0PS9jrwtbiwwUoadpQFaAp2bMcYF3ZULTuKE7Hhi0HMNovVnkfeQl1+FpogiLnhXg+gRXJMaNWRx72ECwegauXMN1pN1MQxQmKVVT2Vr4B+yJEmMuFrKxaycvyQSNqqOwZ0dxwouQSluBBEQGHVBFZq6LRzEOqoB3qVb63oeWpqxc8Doq1SmKB0IPq6UFgtRU0Iy2SQe294uu+Ivk6T10L6WpWJIZtJ20xanXKv0qm+AroLJ1pUFA3zsnoPXV9r1h5KUFSh1QkgY6NMAroLZaN8p9V+TFCQMwcWno5OO60yEVAqE0OGFEnzbL+HMhPIUJcaakCx8MD9EAOrF0oTgNFfKIhemF+N876tBQptsh8n+tHAfUDZCjteKEIdq1ZOQPnY1VdAKbXjoQrRfBfK7sGEoAodAjkXiiD2wDS5UM5QnYUBpQNhWA1e5QHlwmRqd10oOlGrF0q+Bn2NSo5HwWE1a2G2rZhybdTUBpQWVuGWoBwqysQeOq3KlQSlbKMwAworyJUEJV+KyXShvOPDXEW2N4SIpxahmlSi3eo9FFdArzr6ZcV7e9da9bhA9IWy1cMZwb0h2wFFyTVYBRRWCtURUIqLA2ZjwHhBo4c6LsOAUqiQd0J550Mas/BC+RaF1YXCqkPVBpR3hFV7IEdIjoCcraicC2W5whalQDIIJJn1WZOgNIa0Qq4nat0HlJiANnGI5LRKzt54lHk6sHBjoTOBPHaM2oDSGSFOAwqr0zBXx0DDXCgdCFrOoXqgaZ16lSU3IDGEk1w4yWUqGuZCyYq36MQt6gtB9IXCShCql0ldGFDqR96iG7eo6OIYWjVU7RwwGzIqoFjY8JAShQZwifEeytoEGZrooYByOrjwMLVQon6vgsKFaRJQ1Co1pkOoVUHdq+FFEdB17y+k2mmTO06KZmvY0Z8w9UDa8K7VhjdMQJFtCrelGI8BpQOhrQIKK316UvLY9XtOglKG8JlhDlyZLpBrXG4FCg31FVDeEfP+YFhNSv5p0F/uqxbTMozHgHIQNUPbIAJ9Rxzb+CnFIKPcVw3VqHM2ta8yHW7FHkLzOVTca+94i06+03whH/0YtR5WjT6gOHZDPzqET5sXSnzNyr1DzPaEeBjQvm5Vjx1Q3hFxUUdvSp/JF/6mHN6Uw15eD75wgTVkO6A4Np/NviP5egoV7ike9sNXH96ibkUKcSCHMO/3QbsvDmSDQHIrnhEzx6EHKzBxGnqoQ7m7Fc548DnSX0ggB5+7ExRVeCAo3QpQY7YbPpL4mGBcaD5nFb2tGFa+8MEXRq3+AuUzou75bN74w5KgeP0q+Aoo7Qjl7lYsAHzrcKtaTAEl95iFDjFD+GC8FNdcQJFHqg5vbRQm36Jr4QIzvgI2n5RONN3jyvRQme1eM+Q7kgneHUaiJ1M7kdq18AXJHiZ5Q2rdiqxSpS3w5fc2rnLDHxajNnGolpz7QvRKUdM5ajseLO4Lqe2/QEljojsCSu6hAU7rlejTrIZ6Otwf1pdDHDDQAB4XhYjUmjCobX+m1Twm6C48nQe60I+VL6xtlaBsNWg1aKW0UlpNWvFAVHyj1mqCXu6//wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [transaction = _t, prodCode = _t]), groupTransaction = Table.Group(Source, {"transaction"}, {{"data", each _, type table [transaction=nullable text, prodCode=nullable text]}}), addHasProd = Table.AddColumn(groupTransaction, "hasProd", each List.Contains([data][prodCode], "3C013")), filterFalseHasProd = Table.SelectRows(addHasProd, each ([hasProd] = true)) in filterFalseHasProdSummary:
1) Source = Start with just the [transaction] and [productCode] columns - this would be your [Dokument] and [ProdName] columns. This isn't necessary, just simpler to see what's going on.
2) groupTransaction = Group the table on [transaction] using an All Rows aggregation so we have a nested table with all the products on each transaction.
3) addHasProd = Add a new column that reads each of the nested tables to see whether the product we want is there. Instead of my " List.Contains([data][prodCode], "3C013") " code, you would use something like " List.Contains([data][ProdName], "Coca Cola").
4) filterFalseHasProd = Filter out any false values in our new column. This leaves us only transactions that contain the product we want.
From here, it's up to you where you go depending on what you want to achieve. I would probably expand the [data] column nested tables back out to get each product row back and send to the PBI data model to run measures for analysis etc.
Pete