Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Stuki
Frequent Visitor

How to copy data from related line or merge data from related lines together

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_DateTran_idSubsidiaryItem no.SalesCost of SalesSales_Type
01.09.20220001AaaX1 130 
01.09.20220002AaaX1250 Warranty
01.09.20220006AaaR4 45 
01.09.20220007AaaR490 Standard
02.09.20220010AaaM6 69 
02.09.20220011AaaM6135 Standard
03.09.20220024CccX1 44 
03.09.20220025CccX187 Warranty

 

I want to transform the above table into either:

 

Tran_DateTran_idSubsidiaryItem no.SalesCost of SalesSales_Type
01.09.20220001AaaX1 130Warranty
01.09.20220002AaaX1250 Warranty
01.09.20220006AaaR4 45Standard
01.09.20220007AaaR490 Standard
02.09.20220010AaaM6 69Standard
02.09.20220011AaaM6135 Standard
03.09.20220024CccX1  Warranty
03.09.20220025CccX18744Warranty

ie. Sales Type populate for Cost of Sales lines according to the related Sales line.

 

Or:

 

Tran_DateSubsidiaryItem no.SalesCost of SalesSales_Type
01.09.2022AaaX1250130Warranty
01.09.2022AaaR49045Standard
02.09.2022AaaM613569Standard
03.09.2022CccX18744Warranty

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

 

 

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_0-1664869881319.png

In the Group by dialoge, select Max Sales_Type.

vkalyjmsft_1-1664869998687.png

 

2.Select Home>Merge Queries.

vkalyjmsft_8-1664870668801.png

Select the three columns at the same time. In the table option, select current table.

vkalyjmsft_2-1664870182297.png

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"

vkalyjmsft_3-1664870293656.png

vkalyjmsft_6-1664870438909.png

vkalyjmsft_5-1664870393824.png

4.Expand the new column with Group column.

vkalyjmsft_7-1664870533590.png

Get the result:

vkalyjmsft_9-1664870984540.png

 

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.

View solution in original post

7 REPLIES 7
v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_0-1664869881319.png

In the Group by dialoge, select Max Sales_Type.

vkalyjmsft_1-1664869998687.png

 

2.Select Home>Merge Queries.

vkalyjmsft_8-1664870668801.png

Select the three columns at the same time. In the table option, select current table.

vkalyjmsft_2-1664870182297.png

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"

vkalyjmsft_3-1664870293656.png

vkalyjmsft_6-1664870438909.png

vkalyjmsft_5-1664870393824.png

4.Expand the new column with Group column.

vkalyjmsft_7-1664870533590.png

Get the result:

vkalyjmsft_9-1664870984540.png

 

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

serpiva64
Super User
Super User

Hi,

the easiest way is to fill up Sales and Sales Type

serpiva64_0-1664809577969.png

and to fill down Cost of sales

Then you remove alternate rows

serpiva64_1-1664809699090.png

 

serpiva64_2-1664809719619.png

and you get your result

serpiva64_3-1664809777924.png

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors