Forum Discussion
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_TO Transport_Order__c 07_Order Booking date 11_Order D...
- 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"
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
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"
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"