Hi @Krzysiek87 ,
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
chgTypes
You 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
Source
2) 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 @Krzysiek87 ,
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
chgTypes
You 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
Source
2) 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
Fair enough. If you can provide an example of your data in a copyable format and give an indication of exactly the metrics you want to produce, I'm happy to do this based on your actual data.
To provide example data, the best way would be to filter your data table to the top 100 rows, then copy the entire table:
Go to the Home tab > Enter Data > paste into there. Then open the Advanced Editor for that query, copy everything in there, and paste into a code window here ( </> button ).
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
Try copying and pasting your Excel table into Power Query, like this:
Copy your Excel table, go to the Home tab > Enter Data > paste into there.
Then open the Advanced Editor for that query, copy everything in there, and paste into a code window here ( </> button ).
Pete
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
A Year in Review - December 2024
Find out what content was popular in the Fabric community during 2024.
