Forum Discussion
Help with complicated formula required!!!
In case of anyone curious, I've solved this issue. A bit differently than was expecting, but all good with result.
let
Source = Excel.Workbook(Web.Contents("i45WMjC3NDQ2NVDSUXKtSMwtyEkFshyLizPT81JTFByLUhMVDBV0FRKTUoDilvpGFvpGBkaGQLahgb6hIYzjnA/SWZKaApEwMoOrAiqxxKrKUN/YACZhqm8IZhsB2Z55CgFF+elFqcXFSrE6MPcZ4nGf0SBwnxEe9xkPnPtiAQ"), null, true),
query_Sheet = Source{[Item="query",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(query_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Title", type text}, {"BadgeNumber", Int64.Type}, {"Crew", Int64.Type}, {"Operator Name", type text}, {"Department", type text}, {"Section", type text}, {"Unit Start Date", type date}, {"Unit Completion Date", type date}, {"Unit Status", type text}, {"Exam(A) Start Date", type date}, {"Exam(A) Completion Date", type date}, {"Exam(A) Status", type text}, {"Exam(B) Start Date", type date}, {"Exam(B) Completion Date", type date}, {"Exam(B) Status", type text}, {"Overall Status", type text}, {"Senior Training Date", type any}, {"Unit", type text}, {"Comments", type any}, {"Modified By", type text}, {"Mentor1", type text}, {"Mentor2", type text}, {"Intro Written Test Date", type date}, {"Intro Written Test Status", type text}, {"Qualified Written Test Date", type date}, {"Qualified Written Test Status", type text}, {"Modified", type datetime}, {"Item Type", type text}, {"Path", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Department] = "1A")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Sections", each if [Title] = "Unit waste water" then "SEC B" else if [Title] = "Unit Sour Water" then "SEC C" else if [Title] = "Unit Water Separation" then "SEC A" else null),
#"Merged Queries" = Table.NestedJoin(#"Added Custom", {"BadgeNumber"}, #"U courses", {"Personnel ID"}, "U courses", JoinKind.LeftOuter),
#"Expanded U courses" = Table.ExpandTableColumn(#"Merged Queries", "U courses", {"Position Title"}, {"Position Title"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded U courses",{"Section", "Senior Training Date", "Comments", "Modified By", "Mentor1", "Mentor2", "Intro Written Test Date", "Intro Written Test Status", "Qualified Written Test Date", "Qualified Written Test Status", "Modified", "Item Type", "Path", "Unit", "Crew"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"BadgeNumber", "Operator Name", "Unit Start Date", "Unit Completion Date", "Unit Status", "Test(A) Start Date", "Test(A) Completion Date", "Test(A) Status", "Test(B) Start Date", "Test(B) Completion Date", "Test(B) Status", "Overall Status", "Sections", "Position Title"}),
#"Added Custom1" = Table.AddColumn(#"Removed Duplicates", "Custom", each if Text.Contains([Position Title], "SEC B") then "SEC B" else if Text.Contains([Position Title], "SEC A") then "SEC A" else if Text.Contains([Position Title], "SEC C") then "SEC C" else null),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", type text}})
#"Added Conditional Column1" = Table.AddColumn(#"Changed Type1", "Unit Start Date ", each if [Custom] = [Sections] then [Unit Start Date] else null),
#"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Unit Finish Date ", each if [Custom] = [Sections] then [Unit Completion Date] else null),
#"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "Unit Status ", each if [Custom] = [Sections] then [Unit Status] else null),
#"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "Test A Start Date ", each if [Custom] = [Sections] then [#"Test(A) Start Date"] else null),
#"Added Conditional Column5" = Table.AddColumn(#"Added Conditional Column4", "Test A Finish Date ", each if [Custom] = [Sections] then [#"Test(A) Completion Date"] else null),
#"Added Conditional Column6" = Table.AddColumn(#"Added Conditional Column5", "Test A Status ", each if [Custom] = [Sections] then [#"Test(A) Status"] else null),
#"Added Conditional Column7" = Table.AddColumn(#"Added Conditional Column6", "Test B Start Date ", each if [Custom] = [Sections] then [#"Test(B) Start Date"] else null),
#"Added Conditional Column8" = Table.AddColumn(#"Added Conditional Column7", "Test B Finish Date", each if [Custom] = [Sections] then [#"Test(B) Completion Date"] else null),
#"Added Conditional Column9" = Table.AddColumn(#"Added Conditional Column8", "Test B Status ", each if [Custom] = [Sections] then [#"Test(B) Status"] else null),
#"Added Conditional Column10" = Table.AddColumn(#"Added Conditional Column9", "Overall Status ", each if [Custom] = [Sections] then [Overall Status] else null),
#"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column10",{"Unit Start Date", "Unit Completion Date", "Unit Status", "Test(A) Start Date", "Test(A) Completion Date", "Test(A) Status", "Test(B) Start Date", "Test(B) Completion Date", "Test(B) Status", "Overall Status"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Columns1", each ([#"Unit Start Date "] <> null)),
#"Removed Duplicates1" = Table.Distinct(#"Filtered Rows1")
in
#"Removed Duplicates1"
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjC3NDQ2NVDSUXKtSMwtyEkFshyLizPT81JTFByLUhMVDBV0FRKTUoDilvpGFvpGBkaGQLahgb6hIYzjnA/SWZKaApEwMoOrAiqxxKrKUN/YACZhqm8IZhsB2Z55CgFF+elFqcXFSrE6MPcZ4nGf0SBwnxEe9xkPnPtiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BadgeNumber = _t, #"Operator Name" = _t, Unit = _t, #"Unit Start Date" = _t, #"Unit Completion Date" = _t, #"Unit Status" = _t, #"Exam 1 Start Date" = _t, #"Exam 1 Completion Date" = _t, #"Exam 1 Status" = _t, #"Exam 2 Start Date" = _t, #"Exam 2 Completion Date" = _t, #"Exam 2 Status" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Unit Start Date", type date}, {"Unit Completion Date", type date}, {"Exam 1 Start Date", type date}, {"Exam 1 Completion Date", type date}, {"Exam 2 Start Date", type date}, {"Exam 2 Completion Date", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Operator Name", "Unit Status", "Exam 1 Status", "Exam 2 Status"}),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Removed Columns", {{"Unit", each Text.AfterDelimiter(_, " "), type text}}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Extracted Text After Delimiter", {{"Unit", each Text.BeforeDelimiter(_, " -"), type text}})
in
#"Extracted Text Before Delimiter"
If it's somehow helps, here is steps that I've already done:
= Table.ExpandTableColumn(#"Merged Queries5", "query", {"Unit Start Date", "Unit Completion Date", "Unit Status", "Test(A) Start Date", "Test(A) Completion Date", "Test(A) Status", "Test(B) Start Date", "Test(B) Completion Date", "Test(B) Status", "Overall Status"}, {"Unit Start Date", "Unit Completion Date", "Unit Status", "Test(A) Start Date", "Test(A) Completion Date", "Test(A) Status", "Test(B) Start Date", "Test(B) Completion Date", "Test(B) Status", "Overall Status"})
and the problem is formula above give just merging and shows all data (correctly) but I need to add here clarification that I need not all dates, I need to see only certain training by assigned area, so in merged new columns will be only this certain training's and it's tests dates.
As per picture pasted by you, do you need all 3 rows or only 1 row? I think the data pasted by you may not represent the ask of yours.
Can you increase the dataset and so that I may see what you are filtering out? Currently, I see 3 rows inputs and 3 rows output.
In a report itself there are thousands rows, whole training history of the company employees, I need to add dates as per assigned area. So, we have in general 3 dedicated areas with it's speacial unit cources and it's two types of tests (test A and B); All employees having all 3 special units and it's tests (so each employee have 3 unit cources (unit course for area 1, unit course for area 2, unit course for area 3), 3 Test A and 3 test B (2 for each area);
For example, I've assigned by position to area 2, and I've already had all 9 sessions in my training history (with 9 different dates), and I need to add to existing history additional colums where should be shown only unit course for area 2, Test A and B for area 2, cause I'm assigned to area 2.
Hope it helps to clarify....