Solved: Re: combine multiple rows into single row with one...

Anonymous · ‎07-19-2020

Hi All,

Actually im struggling in below case where date needs to be added to all rows of key value of first col that is MST_TO

i tied fill up and fill down doesnt work out for this use case

MST_TO

Transport_Order__c

07_Order Booking date

11_Order Depart (CFS/CY) Origin date

13_Shipment In Gate Origin POL date

14_Shipment Manifest date

17_Shipment POL Depart date

18_Shipment POD Arrival date

19_Shipment POD Discharge date

25_Shipment Freight Available

26_Shipment Out Gate Dest POD date

31_Shipment Out Gate Dest Interim date

36_Shipment Delivered

05_Order Pickup date

01_Order Create date

02_Order Approved date

03_Order Requested Ship date

04_Order Planned date

04_Order Dispatched date

06_Order Docs Validated date

06_Order Docs Received date

07_Order Booking Cancelled date

08_Order Arrival (CFS/CY) Origin date

17_Transship Depart date

17_Transship Arrival date

AAA

DDDD

null

4/10/2020

5/14/2020

5/12/2020

null

AAA

DDDD

null

4/10/2020

5/14/2020

5/12/2020

null

6/18/2020

null

AAA

null

4/10/2020

5/14/2020

5/12/2020

null

4/9/2020

null

4/8/2020

4/10/2020

4/8/2020

4/14/2020

4/9/2020

null

4/10/2020

null

BBB

EEE

null

5/4/2020

5/5/2020

5/7/2020

5/24/2020

5/25/2020

null

5/28/2020

null

5/13/2020

5/9/2020

BBB

FFF

null

5/4/2020

5/5/2020

5/7/2020

5/24/2020

5/25/2020

null

5/28/2020

null

5/13/2020

5/9/2020

CCCC

GGGG

null

3/28/2020

5/22/2020

4/5/2020

5/25/2020

5/26/2020

null

6/4/2020

null

5/19/2020

5/11/2020

CCCC

null

3/27/2020

3/17/2020

3/18/2020

3/25/2020

3/19/2020

4/2/2020

3/27/2020

null

CCCC

HHHH

null

3/28/2020

5/22/2020

4/5/2020

5/25/2020

5/26/2020

null

6/4/2020

null

5/19/2020

5/11/2020

desired output is

MST_TO_NO__c

Transport_Order__c

07_Order Booking date

11_Order Depart (CFS/CY) Origin date

13_Shipment In Gate Origin POL date

14_Shipment Manifest date

17_Shipment POL Depart date

18_Shipment POD Arrival date

19_Shipment POD Discharge date

25_Shipment Freight Available

26_Shipment Out Gate Dest POD date

31_Shipment Out Gate Dest Interim date

36_Shipment Delivered

05_Order Pickup date

01_Order Create date

02_Order Approved date

03_Order Requested Ship date

04_Order Planned date

04_Order Dispatched date

06_Order Docs Validated date

06_Order Docs Received date

07_Order Booking Cancelled date

08_Order Arrival (CFS/CY) Origin date

17_Transship Depart date

17_Transship Arrival date

AAA

DDDD

null

4/10/2020

5/14/2020

5/12/2020

null

6/18/2020

4/9/2020

null

4/8/2020

4/10/2020

4/8/2020

4/14/2020

4/9/2020

null

4/10/2020

null

AAA

DDDD

null

4/10/2020

5/14/2020

5/12/2020

null

6/18/2020

4/9/2020

null

4/8/2020

4/10/2020

4/8/2020

4/14/2020

4/9/2020

null

4/10/2020

null

AAA

null

4/10/2020

5/14/2020

5/12/2020

null

6/18/2020

4/9/2020

null

4/8/2020

4/10/2020

4/8/2020

4/14/2020

4/9/2020

null

4/10/2020

null

BBB

EEE

null

5/4/2020

5/5/2020

5/7/2020

5/24/2020

5/25/2020

null

5/28/2020

null

5/13/2020

5/9/2020

BBB

FFF

null

5/4/2020

5/5/2020

5/7/2020

5/24/2020

5/25/2020

null

5/28/2020

null

5/13/2020

5/9/2020

CCCC

GGGG

null

3/28/2020

5/22/2020

4/5/2020

5/25/2020

5/26/2020

null

6/4/2020

null

3/27/2020

3/17/2020

3/18/2020

3/25/2020

3/19/2020

4/2/2020

3/27/2020

null

5/19/2020

5/11/2020

CCCC

null

3/28/2020

5/22/2020

4/5/2020

5/25/2020

5/26/2020

null

6/4/2020

null

3/27/2020

3/17/2020

3/18/2020

3/25/2020

3/19/2020

4/2/2020

3/27/2020

null

5/19/2020

5/11/2020

CCCC

HHHH

null

3/28/2020

5/22/2020

4/5/2020

5/25/2020

5/26/2020

null

6/4/2020

null

3/27/2020

3/17/2020

3/18/2020

3/25/2020

3/19/2020

4/2/2020

3/27/2020

null

5/19/2020

5/11/2020

AllisonKennedy · ‎07-20-2020

Ok, I think I understand now. To summarize the problem:

Item	Date
AA	null
AA	July 1, 2020
AA	null
BB	null
BB	null
BB	null
BB	July 20, 2020

Desired result:

Item	Date
AA	July 1, 2020
AA	July 1, 2020
AA	July 1, 2020
BB	July 20, 2020
BB	July 20, 2020
BB	July 20, 2020
BB	July 20, 2020

SOLUTION:

My thinking is to group by item, find the MAX Date for each item, then merge back into table. You could possibly create a custom function to do this for you, but here is the M code for the sample data I created to do this. You will need to paste this into three blank queries in Advanced Editor and name the tables as below to see it in action.

RawData

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUVKK1YEwDBW8SnMqdRSMDIwM4IKoDCdkhpEBugYnrOqUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Date", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1)
in
#"Added Index"

FillDate

let
Source = RawData,
#"Grouped Rows" = Table.Group(Source, {"Item"}, {{"Date", each List.Max([Date]), type date}})
in
#"Grouped Rows"

Merge1

let
Source = Table.NestedJoin(RawData, {"Item"}, FillDate, {"Item"}, "FillDate", JoinKind.LeftOuter),
#"Expanded FillDate" = Table.ExpandTableColumn(Source, "FillDate", {"Date"}, {"Date.1"})
in
#"Expanded FillDate"

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

v-rzhou-msft · ‎07-19-2020

Hi @Anonymous

You can use Calculated Columns or Measure to achieve your goal.

You can click this place and create calculated columns or new measure.

Use Column(06_Order Docs Received date) and Column (36_Shipment Delivered) as an example.

1. Calculated columns:

C_06_Order Docs Received date =

CALCULATE(MAX('Table'[06_Order Docs Received date]),FILTER('Table','Table'[MST_TO]=EARLIER('Table'[MST_TO])))

C_36_Shipment Delivered =

CALCULATE(MAX('Table'[36_Shipment Delivered]),FILTER('Table','Table'[MST_TO]=EARLIER('Table'[MST_TO])))

Note:More info about Earlier Function Dax:https://docs.microsoft.com/en-us/dax/earlier-function-dax

2. Measure:

M_06_Order Docs Received date = 
CALCULATE(MAX('Table'[06_Order Docs Received date]),FILTER(ALL('Table'),'Table'[MST_TO]=MAX('Table'[MST_TO])))

M_36_Shipment Delivered = 
CALCULATE(MAX('Table'[36_Shipment Delivered]),FILTER(ALL('Table'),'Table'[MST_TO]=MAX('Table'[MST_TO])))

Result:

Other columns use the same DAX.

You can download the pbix file form this link:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/tongzhou_qiuyunus_onmicrosoft_com/EVbEypVbkD5Nqgyan0RnOZoBjob1rFrCcNcK2j8revxvcg?e=l0dS86

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Ashish_Mathur · ‎07-19-2020

Hi,

Why does the Fill Down not work? WHat problem do you face? Please elaborate.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

AllisonKennedy · ‎07-19-2020

Is it just columns 13, 14, 17 that you're concerned with? Please explain how you know which date to put and why fill up/down won't work,, as from the sample data you provided it looks like it could.
Otherwise this might be a conditional column?

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous · ‎07-19-2020

Hi Allison,

Thanks for checking my concerns

Fill up or fill down will wrongly fills up the data where it should not.

what i want is to have all the date values in all the rows corresponding to only first column only. not to other key.

in my example AAA as MST_TO rows should contain all the dates in all three rows in their rows only

In other words
I can say that for key value of MST_TO number , if Second column Transport_order_cc is null, those values should be combined with both other rows of the key value MST_TO

AllisonKennedy · ‎07-20-2020

Ok, I think I understand now. To summarize the problem:

Item	Date
AA	null
AA	July 1, 2020
AA	null
BB	null
BB	null
BB	null
BB	July 20, 2020

Desired result:

Item	Date
AA	July 1, 2020
AA	July 1, 2020
AA	July 1, 2020
BB	July 20, 2020
BB	July 20, 2020
BB	July 20, 2020
BB	July 20, 2020

SOLUTION:

My thinking is to group by item, find the MAX Date for each item, then merge back into table. You could possibly create a custom function to do this for you, but here is the M code for the sample data I created to do this. You will need to paste this into three blank queries in Advanced Editor and name the tables as below to see it in action.

RawData

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUVKK1YEwDBW8SnMqdRSMDIwM4IKoDCdkhpEBugYnrOqUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Date", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1)
in
#"Added Index"

FillDate

let
Source = RawData,
#"Grouped Rows" = Table.Group(Source, {"Item"}, {{"Date", each List.Max([Date]), type date}})
in
#"Grouped Rows"

Merge1

let
Source = Table.NestedJoin(RawData, {"Item"}, FillDate, {"Item"}, "FillDate", JoinKind.LeftOuter),
#"Expanded FillDate" = Table.ExpandTableColumn(Source, "FillDate", {"Date"}, {"Date.1"})
in
#"Expanded FillDate"

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous · ‎07-22-2020

@AllisonKennedy

I really appreciate your help here. you made my day 🙂

Will try it out on my actual data.

AllisonKennedy · ‎07-23-2020

@Anonymous glad to help. Hope it works well on your data!

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous · ‎07-24-2020

@AllisonKennedy

How do I write my expand the table colum in Merg1 when I have more than 1 date columns from FillDate

FillDate

let
Source = RawData,
#"Grouped Rows" = Table.Group(Source, {"MST_TO_NO__c"}, {{"01_Order Create date", each List.Max([01_Order Create date]), type date},{"02_Order Approved date", each List.Max([02_Order Approved date]), type date}})
in
#"Grouped Rows"

Merge1

let
Source = Table.NestedJoin(Derived_Event, {"MST_TO_NO__c"}, Group_Derived, {"MST_TO_NO__c"}, "Group_Derived", JoinKind.LeftOuter),
#"Expanded FillDate" = Table.ExpandTableColumn(Source, "Group_Derived", {"01_Order Create date"}, {"01_Order Create Date"},{"02_Order Approved date"},{"02_Order Approved Date"} )
in
#"Expanded FillDate"

Anonymous · ‎07-24-2020

@AllisonKennedy

#"Expanded FillDate" = Table.ExpandTableColumn(Source, "Group_Derived", {"01_Order Create date","02_Order Approved date"}, {"01_Order Create Date","02_Order Approved Date"})

I used this in this way, hope its correct

AllisonKennedy · ‎07-24-2020

You can also do this using the Settings cog next to the name of the step in the Applied Steps pane on the right hand side of the Power Query Editor, then you don't need to write custom M code. 🙂

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous · ‎07-27-2020

@AllisonKennedy

how do we do same thing in custom function?

combine multiple rows into single row with one column as unique ID

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly