Untitled

mail@pastecode.io avatar
unknown
plain_text
a year ago
16 kB
1
Indexable
Never
let
    Source = Table.FromRecords({
        [Name="AKJUNC1", Year2016=2016006466, Year2017=2017005800, Year2018=2018005529, Year2019=2019005934, Year2020=2020007385, Year2021=2021007832, Year2022=2022005243, Year2023=2023000932, Code=45020],
        [Name="AKJUNC2", Year2016=2016003665, Year2017=2017004347, Year2018=2018003626, Year2019=2019003392, Year2020=2020003784, Year2021=2021004759, Year2022=2022003253, Year2023=2023000647, Code=45020],
        [Name="AKJUNC3", Year2016=2016001453, Year2017=2017001439, Year2018=2018001258, Year2019=2019001355, Year2020=2020001543, Year2021=2021001754, Year2022=2022001252, Year2023=2023000237, Code=45019],
        [Name="AKJUNC7", Year2016=2016000309, Year2017=2017000382, Year2018=2018000381, Year2019=2019000376, Year2020=2020000378, Year2021=2021000427, Year2022=2022000317, Year2023=2023000094, Code=45019],
        [Name="AKJUNC4", Year2016=2016000554, Year2017=2017000573, Year2018=2018000537, Year2019=2019000441, Year2020=2020000551, Year2021=2021000584, Year2022=2022000463, Year2023=2023000054, Code=45016],
        [Name="AKJUNC5", Year2016=2016000803, Year2017=2017000836, Year2018=2018000739, Year2019=2019000759, Year2020=2020000809, Year2021=2021000950, Year2022=2022000734, Year2023=2023000170, Code=45019],
        [Name="AKJUNC6", Year2016=2016000231, Year2017=2017000220, Year2018=2018000215, Year2019=2019000239, Year2020=2020000271, Year2021=2021000274, Year2022=2022000177, Year2023=2023000028, Code=45016],
        [Name="AKFAIC3", Year2016=2016001334, Year2017=2017001638, Year2018=2018001522, Year2019=2019001256, Year2020=2020001083, Year2021=2021001300, Year2022=2022001238, Year2023=2023000253, Code=45014],
        [Name="AKFAIC5", Year2016=2016000495, Year2017=2017000820, Year2018=2018000739, Year2019=2019000911, Year2020=2020000463, Year2021=2021000894, Year2022=2022000440, Year2023=2023000054, Code=45020],
        [Name="AKANCC1", Year2016=2019039334, Year2017=2017053357, Year2018=2018048571, Year2019=2019049919, Year2020=2020063686, Year2021=2021069557, Year2022=2022045491, Year2023=2023007743, Code=45020],
        [Name="AKKENC1", Year2016=2016011346, Year2017=2017014374, Year2018=2018011678, Year2019=2019011735, Year2020=2020012974, Year2021=2021014268, Year2022=2022010736, Year2023=2023002047, Code=45020],
        [Name="AKANCC4", Year2016=2016002008, Year2017=2017002032, Year2018=2018001920, Year2019=2019002048, Year2020=2020002205, Year2021=2021002425, Year2022=2022001619, Year2023=2023000254, Code=45019],
        [Name="AKANCC6", Year2016=2016000420, Year2017=2017000373, Year2018=2018000451, Year2019=2019000362, Year2020=2020000353, Year2021=2021000366, Year2022=2022000358, Year2023=2023000056, Code=45016],
        [Name="AKANCC8", Year2016=2016000470, Year2017=2017000383, Year2018=2018000417, Year2019=2019000380, Year2020=2020000340, Year2021=2021000380, Year2022=2022000341, Year2023=2023000057, Code=45019],
        [Name="AKANCC7", Year2016=2016000487, Year2017=2017000608, Year2018=2018000484, Year2019=2019000604, Year2020=2020000442, Year2021=2021000424, Year2022=2022000371, Year2023=2023000084, Code=45019],
        [Name="AKANCC5", Year2016=2016000796, Year2017=2017000864, Year2018=2018000832, Year2019=2019000974, Year2020=2020000860, Year2021=2021001003, Year2022=2022000804, Year2023=2023000184, Code=45020],
        [Name="AKANCC2", Year2016=2016003955, Year2017=2017004207, Year2018=2018003901, Year2019=2019003902, Year2020=2020004605, Year2021=2021004895, Year2022=2022004241, Year2023=2023000766, Code=45019],
        [Name="AKANCCA", Year2016=2016000240, Year2017=2017000235, Year2018=2018000207, Year2019=2019000239, Year2020=2020000342, Year2021=2021000242, Year2022=2022000204, Year2023=2023000050, Code=45019],
        [Name="AKMATC1", Year2016=2020011270, Year2017=2017030173, Year2018=2018027242, Year2019=2019030010, Year2020=2020033556, Year2021=2021038294, Year2022=2022028913, Year2023=2023005382, Code=45020],
        [Name="AKANCCB", Year2016=2016000188, Year2017=2017000211, Year2018=2018000208, Year2019=2019000211, Year2020=2020000169, Year2021=2021000189, Year2022=2022000233, Year2023=2023000022, Code=45013],
        [Name="AKANCC3", Year2016=2016001499, Year2017=2017001447, Year2018=2018001338, Year2019=2019001370, Year2020=2020001440, Year2021=2021001426, Year2022=2022001343, Year2023=2023000210, Code=45016],
        [Name="AKANCCC", Year2016=2016000739, Year2017=2017000676, Year2018=2018000608, Year2019=2019000731, Year2020=2020000760, Year2021=2021001406, Year2022=2022000695, Year2023=2023000115, Code=45020],
        [Name="AKANCC9", Year2016=2016000158, Year2017=2017000586, Year2018=2018000208, Year2019=2019000229, Year2020=2020000725, Year2021=2021000165, Year2022=2022000189, Year2023=2023000045, Code=45014],
	    [Name="AKMATC2", Year2016=2016001944, Year2017=2017002306, Year2018=2018001886, Year2019=2019001822, Year2020=2020001978, Year2021=2021002201, Year2022=2022001849, Year2023=2023000402, Code=45020],
	    [Name="AKFAIC1", Year2016=2016020028, Year2017=2017021946, Year2018=2022015322, Year2019=2019022585, Year2020=2020024532, Year2021=2021024313, Year2022=2022019833, Year2023=2023003727, Code=45020],
	    [Name="AKFAIC2", Year2016=2016001172, Year2017=2017001381, Year2018=2018001046, Year2019=2019001046, Year2020=2020001191, Year2021=2021001054, Year2022=2022000925, Year2023=2023000292, Code=45019],
	    [Name="AKFAIC6", Year2016=2016000144, Year2017=2017000330, Year2018=2018000310, Year2019=2019000213, Year2020=2020000224, Year2021=2021000166, Year2022=2022000176, Year2023=2023000040, Code=45019],
	    [Name="AKFAIC7", Year2016=2016000267, Year2017=2017000427, Year2018=2018000303, Year2019=2019000232, Year2020=2020000321, Year2021=2021000527, Year2022=2022000341, Year2023=2023000017, Code=45020],            
        [Name="AKFAIC8", Year2016=2016000223, Year2017=2017000250, Year2018=2018000200, Year2019=2019000174, Year2020=2020000170, Year2021=2021000208, Year2022=2022000400, Year2023=2023000028, Code=45015],
	    [Name="AKFAIC4", Year2016=2016001208, Year2017=2017001683, Year2018=2018001139, Year2019=2019001058, Year2020=2020000605, Year2021=2021000558, Year2022=2022000563, Year2023=2023000112, Code=45013],
	    [Name="AKFAIC9", Year2016=2016000818, Year2017=2017000889, Year2018=2018001375, Year2019=2019000846, Year2020=2020001227, Year2021=2021000666, Year2022=2022000585, Year2023=2023000095, Code=45019],
	    [Name="AKFAICA", Year2016=2016000242, Year2017=2017000244, Year2018=2018000184, Year2019=2019000140, Year2020=2020000216, Year2021=2021000150, Year2022=2022000188, Year2023=2023000020, Code=45013],
	    [Name="AKFAICB", Year2016=2016000044, Year2017=2017000034, Year2018=2018000051, Year2019=2019000022, Year2020=2020000066, Year2021=2021000068, Year2022=2022000037, Year2023=2023000001, Code=44964],
	    [Name="AKFAICC", Year2016=2016000040, Year2017=2017000057, Year2018=2018000062, Year2019=2019000043, Year2020=2020000033, Year2021=2021000138, Year2022=2022000059, Year2023=0, Code=0],
	    [Name="AKKETC1", Year2016=2016003665, Year2017=2017004347, Year2018=2018003626, Year2019=2019003392, Year2020=2020003784, Year2021=2021004759, Year2022=2022003253, Year2023=2023000647, Code=45020],
	    [Name="AKVALC2", Year2016=2016000739, Year2017=2017000676, Year2018=2018000608, Year2019=2019000731, Year2020=2020000760, Year2021=2021001406, Year2022=2022000695, Year2023=2023000115, Code=45020],
	    [Name="AKSITC1", Year2016=2016001453, Year2017=2017001439, Year2018=2018001258, Year2019=2019001355, Year2020=2020001543, Year2021=2021001754, Year2022=2022001252, Year2023=2023000237, Code=45019],
    	[Name="AKBETC1", Year2016=2016001172, Year2017=2017001381, Year2018=2018001046, Year2019=2019001046, Year2020=2020001191, Year2021=2021001054, Year2022=2022000925, Year2023=2023000292, Code=45019],
    	[Name="AKKODC1", Year2016=2016002008, Year2017=2017002032, Year2018=2018001920, Year2019=2019002048, Year2020=2020002205, Year2021=2021002425, Year2022=2022001619, Year2023=2023000254, Code=45019],
    	[Name="AKNOMC1", Year2016=2016001334, Year2017=2017001638, Year2018=2018001522, Year2019=2019001256, Year2020=2020001083, Year2021=2021001300, Year2022=2022001238, Year2023=2023000253, Code=45014]
    }),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Year2023", "Code"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Name"}, AK_LIEN_COURT_TABLE_REFSHEET, {"Column4"}, "AK_LIEN_COURT_TABLE_REFSHEET", JoinKind.LeftOuter),
    #"Expanded AK_LIEN_COURT_TABLE_REFSHEET" = Table.ExpandTableColumn(#"Merged Queries", "AK_LIEN_COURT_TABLE_REFSHEET", {"Column1", "Column3"}, {"AK_LIEN_COURT_TABLE_REFSHEET.Column1", "AK_LIEN_COURT_TABLE_REFSHEET.Column3"}),
    #"Added Conditional Column2" = Table.AddColumn(#"Expanded AK_LIEN_COURT_TABLE_REFSHEET", "Custom", each if [AK_LIEN_COURT_TABLE_REFSHEET.Column1] = "OLD ID" then 2 else 1),
    #"Merged Queries1" = Table.NestedJoin(#"Added Conditional Column2", {"AK_LIEN_COURT_TABLE_REFSHEET.Column3"}, #"Last Doc Input", {"District"}, "Last Doc Input", JoinKind.LeftOuter),
    #"Expanded Last Doc Input" = Table.ExpandTableColumn(#"Merged Queries1", "Last Doc Input", {"Last FTP Image", "Date Recorded3"}, {"Last FTP Image", "Date Recorded3"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Last Doc Input",{"Name", "AK_LIEN_COURT_TABLE_REFSHEET.Column3", "Year2016", "Year2017", "Year2018", "Year2019", "Year2020", "Year2021", "Year2022", "Last FTP Image", "Date Recorded3"}),
    #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns","-","",Replacer.ReplaceText,{"Last FTP Image"}),
    #"Extracted First Characters" = Table.TransformColumns(#"Replaced Value", {{"Last FTP Image", each Text.Start(_, 10), type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted First Characters",{{"Last FTP Image", Int64.Type}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type",{{"Custom", "Custom12"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns2", "Custom", each if [Year2022] = [Last FTP Image] then 0 else [Last FTP Image]),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [Date Recorded3] < #date(2023, 1, 1) then 0 else [Date Recorded3]),
    #"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column1",{{"Custom", "Year2023"}, {"Custom.1", "Year2023 Date Recorded"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Last FTP Image", "Date Recorded3"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"AK_LIEN_COURT_TABLE_REFSHEET.Column3", "AK Court City Code"}, {"Name", "CourtID"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns1",{{"Custom12", Order.Ascending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([Custom12] = 1)),
    #"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows",{"Custom12", "AK_LIEN_COURT_TABLE_REFSHEET.Column1"}),
    #"Merged Queries2" = Table.NestedJoin(#"Removed Columns2", {"AK Court City Code"}, #"Last Doc Filings Max", {"AK DNR Filings.In District:"}, "Last Doc Filings Max", JoinKind.LeftOuter),
    #"Expanded Last Doc Filings Max" = Table.ExpandTableColumn(#"Merged Queries2", "Last Doc Filings Max", {"Last Filing Number"}, {"Last Doc in 2023 Sheet"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Last Doc Filings Max",{"Last Doc in 2023 Sheet", "Year2023", "AK Court City Code"}),
    #"Renamed Columns5" = Table.RenameColumns(#"Removed Other Columns",{{"Last Doc in 2023 Sheet", "Last Doc In 2023 Sheet"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns5",{{"Last Doc In 2023 Sheet", Int64.Type}, {"Year2023", Int64.Type}}),
    #"Filtered Rows6" = Table.SelectRows(#"Changed Type2", each [Last Doc In 2023 Sheet] >= 2023000000 and [AK Court City Code] <> null),
    #"Added to Column" = Table.TransformColumns(#"Filtered Rows6", {{"Last Doc In 2023 Sheet", each _ + 1, type number}}),
    #"Added Custom Column" = Table.AddColumn(#"Added to Column", "Numbers List", each List.Numbers([Year2023] + 1, [Last Doc In 2023 Sheet] - [Year2023])),
    #"Renamed Columns6" = Table.RenameColumns(#"Added Custom Column",{{"Numbers List", "List"}}),
    #"Expanded Numbers List" = Table.ExpandListColumn(#"Renamed Columns6", "List"),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Numbers List", each ([List] <> null)),
    #"Changed Type6" = Table.TransformColumnTypes(#"Filtered Rows1",{{"List", Int64.Type}}),
    #"Removed Columns6" = Table.RemoveColumns(#"Changed Type6",{"Year2023", "Last Doc In 2023 Sheet"}),
    #"Renamed Columns7" = Table.RenameColumns(#"Removed Columns6",{{"AK Court City Code", "Attribute"}, {"List", "Value"}}),
    #"Sorted Rows6" = Table.Sort(#"Renamed Columns7",{{"Value", Order.Ascending}}),
    #"Filtered Rows2" = Table.SelectRows(#"Sorted Rows6", each ([Value] <> "")),
    #"Extracted First Characters6" = Table.TransformColumns(#"Filtered Rows2", {{"Attribute", each Text.Start(_, 3), type text}}),
    #"Inserted Prefix" = Table.AddColumn(#"Extracted First Characters6", "Prefix", each "http://dnr.alaska.gov/ssd/recoff/search/assocdocs?district=" & [Attribute], type text),
    #"Added Suffix" = Table.TransformColumns(#"Inserted Prefix", {{"Prefix", each _ & "&associateddoc=", type text}}),
    #"Added Custom" = Table.AddColumn(#"Added Suffix", "Custom", each "0"),
    #"Inserted Merged Column" = Table.AddColumn(#"Added Custom", "Merged", each Text.Combine({[Prefix], Text.From([Value], "en-US"), [Custom]}, ""), type text),
    #"Removed Columns26" = Table.RemoveColumns(#"Inserted Merged Column",{"Prefix", "Custom"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns26", {{"Value", type text}}, "en-US"),{"Attribute", "Value"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"#"),
    #"Renamed Columns26" = Table.RenameColumns(#"Merged Columns",{{"Merged", "URL"}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns26", "Last Doc Inputfx", each #"Last Doc Inputfx"([URL])),
    #"Added Conditional Column3" = Table.AddColumn(#"Invoked Custom Function", "Custom", each if [Last Doc Inputfx] = "Error" then "Error" else [Last Doc Inputfx]),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Conditional Column3", {"Last Doc Inputfx"}),
    #"Expanded Last Doc Inputfx1" = Table.ExpandTableColumn(#"Removed Errors", "Last Doc Inputfx", {"Document Number", "Date Recorded", "Document Index", "Document Description", "Status"}, {"Document Number", "Date Recorded", "Document Index", "Document Description", "Status"}),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Expanded Last Doc Inputfx1", "Text Between Delimiters", each Text.BetweenDelimiters([URL], "=", "&"), type text),
    #"Reordered Columns6" = Table.ReorderColumns(#"Inserted Text Between Delimiters",{"#", "URL", "Text Between Delimiters", "Document Number", "Date Recorded", "Document Index", "Document Description"}),
    #"Renamed Columns4" = Table.RenameColumns(#"Reordered Columns6",{{"Text Between Delimiters", "In District:"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns4",{{"In District:", Int64.Type}}),
    #"Merged Queries6" = Table.NestedJoin(#"Changed Type1", {"In District:"}, AK_REF_SHEET, {"In District:"}, "AK_REF_SHEET", JoinKind.LeftOuter),
    #"Expanded AK_REF_SHEET" = Table.ExpandTableColumn(#"Merged Queries6", "AK_REF_SHEET", {"In District2:"}, {"AK_REF_SHEET.In District2:"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Expanded AK_REF_SHEET",{"#", "URL", "In District:", "AK_REF_SHEET.In District2:", "Document Number", "Date Recorded", "Document Index", "Document Description"}),
    #"Removed Columns3" = Table.RemoveColumns(#"Reordered Columns1",{"AK_REF_SHEET.In District2:", "Custom", "Status"})
in
    #"Removed Columns3"