Skip to content

Instantly share code, notes, and snippets.

@ChandanShakya
Last active May 17, 2024 19:19
Show Gist options
  • Save ChandanShakya/c1a4bc558de0f5f129b8f78cf6c27518 to your computer and use it in GitHub Desktop.
Save ChandanShakya/c1a4bc558de0f5f129b8f78cf6c27518 to your computer and use it in GitHub Desktop.
power query 6th semester
let
Source = Pdf.Tables(File.Contents("C:\Users\Acer\Downloads\2019-Batch-6th-Semester.pdf"), [Implementation="1.3"]),
Table081 = Source{[Id="Table081"]}[Data],
Table082 = Source{[Id="Table082"]}[Data],
Table083 = Source{[Id="Table083"]}[Data],
#"Changed Type 81" = Table.TransformColumnTypes(Table081,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}}),
#"Changed Type 82" = Table.TransformColumnTypes(Table082,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type number}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type number}}),
#"Changed Type 83" = Table.TransformColumnTypes(Table083,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type number}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type number}}),
CombinedTables = Table.Combine({#"Changed Type 81", #"Changed Type 82", #"Changed Type 83"}),
#"Removed Top Rows" = Table.Skip(CombinedTables,14),
#"Kept First Rows" = Table.FirstN(#"Removed Top Rows",33),
#"Changed Type" = Table.TransformColumnTypes(#"Kept First Rows",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type any}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type any}}),
#"Removed Alternate Rows" = Table.AlternateRows(#"Changed Type",27,1,32),
#"Renamed Columns" = Table.RenameColumns(#"Removed Alternate Rows",{{"Column1", "SymbolNo"}, {"Column2", "Full Name"}, {"Column3", "TU Registration No"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","6-2-1-178-2019","6-2-551-178-2019",Replacer.ReplaceText,{"TU Registration No"}),
#"Renamed Columns1" = Table.RenameColumns(#"Replaced Value",{{"Column4", "GPA1"}, {"Column5", "Grade1"}, {"Column6", "GPA2"}, {"Column7", "Grade2"}, {"Column8", "GPA3"}, {"Column9", "Grade3"}, {"Column10", "GPA4"}, {"Column11", "Grade4"}, {"Column12", "GPA5"}, {"Column13", "Grade5"}, {"Column14", "GPA6"}, {"Column15", "Grade6"}, {"Column16", "SGPA"}}),
#"Capitalized Each Word" = Table.TransformColumns(#"Renamed Columns1",{{"Full Name", Text.Proper, type text}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Capitalized Each Word",{{"GPA1", type text}, {"Grade1", type text}, {"GPA2", type text}, {"Grade2", type text}, {"GPA3", type text}, {"Grade3", type text}, {"GPA4", type text}, {"Grade4", type text}, {"GPA5", type text}, {"Grade5", type text}, {"GPA6", type text}, {"Grade6", type text}, {"SGPA", type text}})
in
#"Changed Type1"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment