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
Anonymous
Not applicable

Need help in merging data

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

 

MST_TOTransport_Order__c07_Order Booking date11_Order Depart (CFS/CY) Origin date13_Shipment In Gate Origin POL date14_Shipment Manifest date17_Shipment POL Depart date18_Shipment POD Arrival date19_Shipment POD Discharge date25_Shipment Freight Available26_Shipment Out Gate Dest POD date31_Shipment Out Gate Dest Interim date36_Shipment Delivered05_Order Pickup date01_Order Create date02_Order Approved date03_Order Requested Ship date04_Order Planned date04_Order Dispatched date06_Order Docs Validated date06_Order Docs Received date07_Order Booking Cancelled date08_Order Arrival (CFS/CY) Origin date17_Transship Depart date17_Transship Arrival date
AAADDDDnullnull4/10/20205/14/20205/12/2020nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull
AAADDDDnullnull4/10/20205/14/20205/12/2020nullnullnullnullnull6/18/2020nullnullnullnullnullnullnullnullnullnullnullnull
AAA nullnull4/10/20205/14/20205/12/2020nullnullnullnullnullnull4/9/2020null4/8/20204/10/20204/8/20204/8/20204/14/20204/9/2020null4/10/2020nullnull
BBBEEEnullnull5/4/20205/5/20205/7/20205/24/20205/25/2020null5/28/2020nullnullnullnullnullnullnullnullnullnullnullnull5/13/20205/9/2020
BBBFFFnullnull5/4/20205/5/20205/7/20205/24/20205/25/2020null5/28/2020nullnullnullnullnullnullnullnullnullnullnullnull5/13/20205/9/2020
CCCCGGGGnullnull3/28/20205/22/20204/5/20205/25/20205/26/2020null6/4/2020nullnullnullnullnullnullnullnullnullnullnullnull5/19/20205/11/2020
CCCC nullnullnullnullnullnullnullnullnullnullnull3/27/20203/17/20203/18/20203/25/20203/25/20203/19/20204/2/20203/27/2020nullnullnullnull
CCCCHHHHnullnull3/28/20205/22/20204/5/20205/25/20205/26/2020null6/4/2020nullnullnullnullnullnullnullnullnullnullnullnull5/19/20205/11/2020

 

desired output is 

 

 

 

MST_TO_NO__cTransport_Order__c07_Order Booking date11_Order Depart (CFS/CY) Origin date13_Shipment In Gate Origin POL date14_Shipment Manifest date17_Shipment POL Depart date18_Shipment POD Arrival date19_Shipment POD Discharge date25_Shipment Freight Available26_Shipment Out Gate Dest POD date31_Shipment Out Gate Dest Interim date36_Shipment Delivered05_Order Pickup date01_Order Create date02_Order Approved date03_Order Requested Ship date04_Order Planned date04_Order Dispatched date06_Order Docs Validated date06_Order Docs Received date07_Order Booking Cancelled date08_Order Arrival (CFS/CY) Origin date17_Transship Depart date17_Transship Arrival date
AAADDDDnullnull4/10/20205/14/20205/12/2020nullnullnullnullnull6/18/20204/9/2020null4/8/20204/10/20204/8/20204/8/20204/14/20204/9/2020null4/10/2020nullnull
AAADDDDnullnull4/10/20205/14/20205/12/2020nullnullnullnullnull6/18/20204/9/2020null4/8/20204/10/20204/8/20204/8/20204/14/20204/9/2020null4/10/2020nullnull
AAA nullnull4/10/20205/14/20205/12/2020nullnullnullnullnull6/18/20204/9/2020null4/8/20204/10/20204/8/20204/8/20204/14/20204/9/2020null4/10/2020nullnull
BBBEEEnullnull5/4/20205/5/20205/7/20205/24/20205/25/2020null5/28/2020nullnullnullnullnullnullnullnullnullnullnullnull5/13/20205/9/2020
BBBFFFnullnull5/4/20205/5/20205/7/20205/24/20205/25/2020null5/28/2020nullnullnullnullnullnullnullnullnullnullnullnull5/13/20205/9/2020
CCCCGGGGnullnull3/28/20205/22/20204/5/20205/25/20205/26/2020null6/4/2020nullnull3/27/20203/17/20203/18/20203/25/20203/25/20203/19/20204/2/20203/27/2020nullnull5/19/20205/11/2020
CCCC nullnull3/28/20205/22/20204/5/20205/25/20205/26/2020null6/4/2020nullnull3/27/20203/17/20203/18/20203/25/20203/25/20203/19/20204/2/20203/27/2020nullnull5/19/20205/11/2020
CCCCHHHHnullnull3/28/20205/22/20204/5/20205/25/20205/26/2020null6/4/2020nullnull3/27/20203/17/20203/18/20203/25/20203/25/20203/19/20204/2/20203/27/2020nullnull5/19/20205/11/2020
1 ACCEPTED SOLUTION

You might still be able to do this with Fill Down if you sort on MST_TO and Date columns first, otherwise see if this does what you need:

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

5 REPLIES 5
lbendlin
Super User
Super User

In Power Query there is a transform for that, called "Fill Down".  Depending on your data you might also need to add a "Fill Up"  step.

Anonymous
Not applicable

that would not solve the problem here as it fills up all the below or above columns irrespective of first column as key value consideration

You can sort by first column, then try fill down? We need more info here to help you.

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
Not applicable

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

sanjusanj21_0-1595214473368.png

 

You might still be able to do this with Fill Down if you sort on MST_TO and Date columns first, otherwise see if this does what you need:

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

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.