Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help with complicated formula required!!!

Hello All!   Have an issue with complicated formula as I'm a new user.  So issue is: I've got few trainings assigned to an employee, have it's dates and etc. But I can't get exact data I want from...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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"

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Probably it's because of the way how I'mm explaining...

Required outcome: By employee badge# (column A) and his assigned area (column M) add additionally lines with training dates (clomuns D,E,G,H,I,J) plus to all columns already existing (so new dates will be added from N to S) 

so income:

and outcome should look like:

 

 

 

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

Check this out

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"}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"BadgeNumber", "Unit"}, #"Removed Columns", {"BadgeNumber", "Unit"}, "Removed Columns", JoinKind.LeftOuter),
    #"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {"Unit", "Unit Start Date", "Unit Completion Date", "Exam 1 Start Date", "Exam 1 Completion Date", "Exam 2 Start Date", "Exam 2 Completion Date"}, {"Unit.1", "Unit Start Date.1", "Unit Completion Date.1", "Exam 1 Start Date.1", "Exam 1 Completion Date.1", "Exam 2 Start Date.1", "Exam 2 Completion Date.1"}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Expanded Removed Columns", {{"Unit.1", each Text.AfterDelimiter(_, " "), type text}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Extracted Text After Delimiter", {{"Unit.1", each Text.BeforeDelimiter(_, " -"), type text}})
in
    #"Extracted Text Before Delimiter"