Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I am fairly new to Power Query and am struggling with how to manipulate data within a table in the following way...
Basically, each row shows either the sales or cost of sales element of a transaction. I want to identify all records which are Warranty sales (ie. both the sales element and the cost of sales element). The issue is that at the moment the Sales_Type field, which identifies whether a transaction is for Warranty sale or not, only has value on the Sales transaction lines. ie. the Cost of Sales transactions lines are blank in the Sales_Type field. There is no uniquie identifier that relates to both the sales and cost of sales lines. However, they are numerically sequenced so that the Tran_id for the sales line is always one greater than the Tran_id for the Cost of Sales line.
Initial data:
Tran_Date | Tran_id | Subsidiary | Item no. | Sales | Cost of Sales | Sales_Type |
01.09.2022 | 0001 | Aaa | X1 | 130 | ||
01.09.2022 | 0002 | Aaa | X1 | 250 | Warranty | |
01.09.2022 | 0006 | Aaa | R4 | 45 | ||
01.09.2022 | 0007 | Aaa | R4 | 90 | Standard | |
02.09.2022 | 0010 | Aaa | M6 | 69 | ||
02.09.2022 | 0011 | Aaa | M6 | 135 | Standard | |
03.09.2022 | 0024 | Ccc | X1 | 44 | ||
03.09.2022 | 0025 | Ccc | X1 | 87 | Warranty |
I want to transform the above table into either:
Tran_Date | Tran_id | Subsidiary | Item no. | Sales | Cost of Sales | Sales_Type |
01.09.2022 | 0001 | Aaa | X1 | 130 | Warranty | |
01.09.2022 | 0002 | Aaa | X1 | 250 | Warranty | |
01.09.2022 | 0006 | Aaa | R4 | 45 | Standard | |
01.09.2022 | 0007 | Aaa | R4 | 90 | Standard | |
02.09.2022 | 0010 | Aaa | M6 | 69 | Standard | |
02.09.2022 | 0011 | Aaa | M6 | 135 | Standard | |
03.09.2022 | 0024 | Ccc | X1 | Warranty | ||
03.09.2022 | 0025 | Ccc | X1 | 87 | 44 | Warranty |
ie. Sales Type populate for Cost of Sales lines according to the related Sales line.
Or:
Tran_Date | Subsidiary | Item no. | Sales | Cost of Sales | Sales_Type |
01.09.2022 | Aaa | X1 | 250 | 130 | Warranty |
01.09.2022 | Aaa | R4 | 90 | 45 | Standard |
02.09.2022 | Aaa | M6 | 135 | 69 | Standard |
03.09.2022 | Ccc | X1 | 87 | 44 | Warranty |
ie. Merge lines to show both the Sales figure and the related Cost of Sales figures on the same line.
I really appreciate any help!
Thanks
Stuki
Solved! Go to Solution.
Hi @Stuki ,
According to your description, here's my solution.
1.Select Tran_Date, Subsidiary and Item no. columns at the same time, then click Group By.
In the Group by dialoge, select Max Sales_Type.
2.Select Home>Merge Queries.
Select the three columns at the same time. In the table option, select current table.
3.Then in the code of the merge quries, replace the first table with the step's name before "Grouped Rows", here in my sample, it's "Changed Type1"
4.Expand the new column with Group column.
Get the result:
Here's the whole M syntax:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZDNDoIwEIRfhfRMyLZQkKPBiwcvQqIJIWSFmpAoGKgH3t7ybxEP7c6232Q2G8ckqrFMDygFMQdd5EqF71tT5AXWrWqOUjyNsrK6d3yIRtWgaqRR3Y2p72satS9BEjMmQC3wLQaMqT8AoKrsEdV97aShDrWhV1s403HGYTRdsFYjynbT5c6uszPyDv+b4em0P0WEEssc63wwMc1EYTad3JF3/SViRVOdpjbfzLA1F+umCbLse1eOs2SsaK7TO+9nU8kH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Tran_Date", type date}, {"Tran_id", Int64.Type}, {"Subsidiary", type text}, {"Item no.", type text}, {"Sales", Int64.Type}, {"Cost of Sales", Int64.Type}, {"Sales_Type", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"Tran_Date", "Subsidiary", "Item no."}, {{"Group", each List.Max([Sales_Type]), type nullable text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Tran_Date", "Subsidiary", "Item no."}, #"Grouped Rows", {"Tran_Date", "Subsidiary", "Item no."}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Group"}, {"Grouped Rows.Group"})
in
#"Expanded Grouped Rows"
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Stuki ,
According to your description, here's my solution.
1.Select Tran_Date, Subsidiary and Item no. columns at the same time, then click Group By.
In the Group by dialoge, select Max Sales_Type.
2.Select Home>Merge Queries.
Select the three columns at the same time. In the table option, select current table.
3.Then in the code of the merge quries, replace the first table with the step's name before "Grouped Rows", here in my sample, it's "Changed Type1"
4.Expand the new column with Group column.
Get the result:
Here's the whole M syntax:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZDNDoIwEIRfhfRMyLZQkKPBiwcvQqIJIWSFmpAoGKgH3t7ybxEP7c6232Q2G8ckqrFMDygFMQdd5EqF71tT5AXWrWqOUjyNsrK6d3yIRtWgaqRR3Y2p72satS9BEjMmQC3wLQaMqT8AoKrsEdV97aShDrWhV1s403HGYTRdsFYjynbT5c6uszPyDv+b4em0P0WEEssc63wwMc1EYTad3JF3/SViRVOdpjbfzLA1F+umCbLse1eOs2SsaK7TO+9nU8kH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Tran_Date", type date}, {"Tran_id", Int64.Type}, {"Subsidiary", type text}, {"Item no.", type text}, {"Sales", Int64.Type}, {"Cost of Sales", Int64.Type}, {"Sales_Type", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"Tran_Date", "Subsidiary", "Item no."}, {{"Group", each List.Max([Sales_Type]), type nullable text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Tran_Date", "Subsidiary", "Item no."}, #"Grouped Rows", {"Tran_Date", "Subsidiary", "Item no."}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Group"}, {"Grouped Rows.Group"})
in
#"Expanded Grouped Rows"
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi _ kalyj,
Thank you so much! That does seem to work and I really appreciate the clear instructions. I've got to admit I don't really understand how / why it works. Please could you explain the what the Max Sales_Type does and what merging the table with itself (?) does?
Thanks
Stuki
Hi @Stuki ,
It's my pleasure!
1.As each group (same Tran_Date, Subsidiary and Item no.) only has one Sales_Type, here you can use max/min/average to get the current Sales_Type, it has no matter with maximum.
2.The step merge actually aims to merge the original table and the group table, I use merging the table with itself and then modify the code is because I think it's more easy for you to handle it.
Best Regards,
Community Support Team _ kalyj
Hi,
the easiest way is to fill up Sales and Sales Type
and to fill down Cost of sales
Then you remove alternate rows
and you get your result
If this post is useful to help you to solve your issue consider giving the post a thumbs up
and accepting it as a solution !
Hi serpiva64,
Thanks for the suggestion. I like the simplicity of it. However, in my attempt to provide some dummy data I oversimplified the actual nature of it. ie. the sales and cost of sales rows aren't all neatly paired like my dummy data suggested. There also credit lines (for both sales and cost of sales) in the data. Also, the order can vary. Given these complications is there any way of varying your solution to work?
Thanks
Stuki
Hi,
can you give an exemple of this complex data. In this type of problems it is not impossible to try to find a solution.
Hi serpiva 64,
I'm still interested in what you can suggest. The data seems to be organised as follows:
- the sales lines have a tran_id that is one more than the corresponding cost of sale tran_id. Ideally I would like to copy the cost of sale data to the sales line and filter out the cost of sale line. (of course, the other way round work also work).
- there are credit lines for both sales lines that don't have any corresponding cost of sale credit line. The other way round also exists. Not sure why that is but I have double checked and it is the case. Nothing needs to be done to these lines.
I'll continue to analyse the data but those seem to be the main complexities.
thanks
Stuki
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.