Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 | null | 4/10/2020 | 5/14/2020 | 5/12/2020 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null |
AAA | DDDD | null | null | 4/10/2020 | 5/14/2020 | 5/12/2020 | null | null | null | null | null | 6/18/2020 | null | null | null | null | null | null | null | null | null | null | null | null |
AAA | null | null | 4/10/2020 | 5/14/2020 | 5/12/2020 | null | null | null | null | null | null | 4/9/2020 | null | 4/8/2020 | 4/10/2020 | 4/8/2020 | 4/8/2020 | 4/14/2020 | 4/9/2020 | null | 4/10/2020 | null | null | |
BBB | EEE | null | null | 5/4/2020 | 5/5/2020 | 5/7/2020 | 5/24/2020 | 5/25/2020 | null | 5/28/2020 | null | null | null | null | null | null | null | null | null | null | null | null | 5/13/2020 | 5/9/2020 |
BBB | FFF | null | null | 5/4/2020 | 5/5/2020 | 5/7/2020 | 5/24/2020 | 5/25/2020 | null | 5/28/2020 | null | null | null | null | null | null | null | null | null | null | null | null | 5/13/2020 | 5/9/2020 |
CCCC | GGGG | null | null | 3/28/2020 | 5/22/2020 | 4/5/2020 | 5/25/2020 | 5/26/2020 | null | 6/4/2020 | null | null | null | null | null | null | null | null | null | null | null | null | 5/19/2020 | 5/11/2020 |
CCCC | null | null | null | null | null | null | null | null | null | null | null | 3/27/2020 | 3/17/2020 | 3/18/2020 | 3/25/2020 | 3/25/2020 | 3/19/2020 | 4/2/2020 | 3/27/2020 | null | null | null | null | |
CCCC | HHHH | null | null | 3/28/2020 | 5/22/2020 | 4/5/2020 | 5/25/2020 | 5/26/2020 | null | 6/4/2020 | null | null | null | null | null | null | null | null | null | null | null | 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 | null | 4/10/2020 | 5/14/2020 | 5/12/2020 | null | null | null | null | null | 6/18/2020 | 4/9/2020 | null | 4/8/2020 | 4/10/2020 | 4/8/2020 | 4/8/2020 | 4/14/2020 | 4/9/2020 | null | 4/10/2020 | null | null |
AAA | DDDD | null | null | 4/10/2020 | 5/14/2020 | 5/12/2020 | null | null | null | null | null | 6/18/2020 | 4/9/2020 | null | 4/8/2020 | 4/10/2020 | 4/8/2020 | 4/8/2020 | 4/14/2020 | 4/9/2020 | null | 4/10/2020 | null | null |
AAA | null | null | 4/10/2020 | 5/14/2020 | 5/12/2020 | null | null | null | null | null | 6/18/2020 | 4/9/2020 | null | 4/8/2020 | 4/10/2020 | 4/8/2020 | 4/8/2020 | 4/14/2020 | 4/9/2020 | null | 4/10/2020 | null | null | |
BBB | EEE | null | null | 5/4/2020 | 5/5/2020 | 5/7/2020 | 5/24/2020 | 5/25/2020 | null | 5/28/2020 | null | null | null | null | null | null | null | null | null | null | null | null | 5/13/2020 | 5/9/2020 |
BBB | FFF | null | null | 5/4/2020 | 5/5/2020 | 5/7/2020 | 5/24/2020 | 5/25/2020 | null | 5/28/2020 | null | null | null | null | null | null | null | null | null | null | null | null | 5/13/2020 | 5/9/2020 |
CCCC | GGGG | null | null | 3/28/2020 | 5/22/2020 | 4/5/2020 | 5/25/2020 | 5/26/2020 | null | 6/4/2020 | null | null | 3/27/2020 | 3/17/2020 | 3/18/2020 | 3/25/2020 | 3/25/2020 | 3/19/2020 | 4/2/2020 | 3/27/2020 | null | null | 5/19/2020 | 5/11/2020 |
CCCC | null | null | 3/28/2020 | 5/22/2020 | 4/5/2020 | 5/25/2020 | 5/26/2020 | null | 6/4/2020 | null | null | 3/27/2020 | 3/17/2020 | 3/18/2020 | 3/25/2020 | 3/25/2020 | 3/19/2020 | 4/2/2020 | 3/27/2020 | null | null | 5/19/2020 | 5/11/2020 | |
CCCC | HHHH | null | null | 3/28/2020 | 5/22/2020 | 4/5/2020 | 5/25/2020 | 5/26/2020 | null | 6/4/2020 | null | null | 3/27/2020 | 3/17/2020 | 3/18/2020 | 3/25/2020 | 3/25/2020 | 3/19/2020 | 4/2/2020 | 3/27/2020 | null | null | 5/19/2020 | 5/11/2020 |
Solved! Go to Solution.
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"
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
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:
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.
Hi,
Why does the Fill Down not work? WHat problem do you face? Please elaborate.
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
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
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"
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
@AllisonKennedy
I really appreciate your help here. you made my day 🙂
Will try it out on my actual data.
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
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"
#"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
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