Forum Discussion
changing the source to SQL table from CSV file
Hi, I am trying to edit the M code to change the source from csv to directly from SQL server. but, i am losing my custom columns. If i just changing the source, it is throwing error. I am quite n...
Anonymous
You could create another query for getting the data from the SQL table. Make sure you have all the columns are available in SQL as well. Column names should be the same. Then go to advance editor and copy PowerQuery. Then replace the initial part of your existing query.
Example
let Source = Sql.Databases("Your source address"), #"Databasename" = Source{[Name="Your Data base name"]}[Data], dbo_YourTableName = #"Databasename"{[Schema="dbo",Item="YourTableName"]}[Data], #"Added Custom" = Table.AddColumn(#"dbo_YourTableName", "Aircraft", each if Text.Contains([Rank], "320") then "320" else if Text.Contains([Rank], "321") then "320" else "ATR"), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Position", each if Text.Contains([Rank], "CP") then "CP" else "FO"), #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each true), #"Added Custom2" = Table.AddColumn(#"Filtered Rows", "Status_key", each if Text.Contains([STATUS], "Line Pilot") then 1 else if Text.Contains([STATUS], "Management Pilot") then 1 else if Text.Contains([STATUS], "Trainer") then 1 else if [TrainingQualfication] = "SFI" then 0 else 0), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Status_Key1", each if [TrainingQualfication] = "SFI" then 0 else [Status_key]), #"Filtered Rows1" = Table.SelectRows(#"Added Custom3", each true), #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Status_key", Int64.Type}}), Custom1 = #"Changed Type1" in Custom1
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
Anonymous
You could create another query for getting the data from the SQL table. Make sure you have all the columns are available in SQL as well. Column names should be the same. Then go to advance editor and copy PowerQuery. Then replace the initial part of your existing query.
Example
let
Source = Sql.Databases("Your source address"),
#"Databasename" = Source{[Name="Your Data base name"]}[Data],
dbo_YourTableName = #"Databasename"{[Schema="dbo",Item="YourTableName"]}[Data],
#"Added Custom" = Table.AddColumn(#"dbo_YourTableName", "Aircraft", each if Text.Contains([Rank], "320") then "320" else
if Text.Contains([Rank], "321") then "320" else "ATR"),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Position", each if Text.Contains([Rank], "CP") then "CP" else "FO"),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each true),
#"Added Custom2" = Table.AddColumn(#"Filtered Rows", "Status_key", each if Text.Contains([STATUS], "Line Pilot") then 1 else if Text.Contains([STATUS], "Management Pilot") then 1 else if Text.Contains([STATUS], "Trainer") then 1 else if [TrainingQualfication] = "SFI" then 0 else 0),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Status_Key1", each if [TrainingQualfication] = "SFI" then 0 else [Status_key]),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom3", each true),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Status_key", Int64.Type}}),
Custom1 = #"Changed Type1"
in
Custom1
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
Thank you so much, worked like a charm 🙂
nandukrishnavs One more query. In the above M code, I am trying to enter a custom SQL query as enclosed. Its throwing me error. Can you help.
This is the mcode I am trying to edit
let Source = Sql.Databases("SQLMPP-PRD-LSNR,53403"), #"Databasename" = Source{[Name="MPPFDA"]}[Data], dbo_YourTableName = #"Databasename"{[Schema="dbo",Item="pilotheadcount_datestamp"]}[Data], #"Added Custom" = Table.AddColumn(#"dbo_YourTableName", "Aircraft", each if Text.Contains([Rank], "320") then "320" else if Text.Contains([Rank], "321") then "320" else "ATR"), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Position", each if Text.Contains([Rank], "CP") then "CP" else "FO"), #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each true), #"Added Custom2" = Table.AddColumn(#"Filtered Rows", "Status_key", each if Text.Contains([STATUS], "Line Pilot") then 1 else if Text.Contains([STATUS], "Management Pilot") then 1 else if Text.Contains([STATUS], "Trainer") then 1 else if [TrainingQualfication] = "SFI" then 0 else 0), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Status_Key1", each if [TrainingQualfication] = "SFI" then 0 else [Status_key]), #"Filtered Rows1" = Table.SelectRows(#"Added Custom3", each true), #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Status_key", Int64.Type}}), Custom1 = #"Changed Type1", #"Added Custom4" = Table.AddColumn(Custom1, "New Contract", each if [Contract] = "FD IN 56 DAYS PL CONTRACT" then "FD IN 11 W ON/ 2 W OFF" else if [Contract] = "COCKPIT CREW ON 80 HRS CONTRACT,FD EX 6 WEEKS ON 2 WEEKS OFF (85 HRS)" then "FD EX 6 WEEKS ON 2 WEEKS OFF (85 HRS)" else if [Contract] = "COCKPIT CREW ON 80 HRS CONTRACT,FD EX 10W ON / 4W OFF" then "FD EX 10W ON / 4W OFF" else if [Contract] = "COCKPIT CREW ON 80 HRS CONTRACT,FD EX 12 WEEKS ON 4 WEEKS OFF" then "FD EX 12 WEEKS ON 4 WEEKS OFF" else if [Contract] = "COCKPIT CREW ON 80 HRS CONTRACT,FD EX 1M ON / 4W OFF" then "FD EX 1M ON / 4W OFF" else if [Contract] = "IN COCKPIT CREW ON 8 DAYS OFF CONTRACT,1 WEEK OFF IN MONTH" then "1 WEEK OFF IN MONTH" else if [Contract] = null then "22 DAYS PL" else [Contract]), #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Short Contract", each if [New Contract] = "1 WEEK OFF IN MONTH" then "84 PL" else if [New Contract] = "22 DAYS PL" then "22 PL" else if [New Contract] = "42 DAYS PL" then "42 PL" else if [New Contract] = "CAPT ON 8 OFF CONTRACT" then "8 Day OFF" else if [New Contract] = "COCKPIT CREW ON 80 HRS CONTRACT" then "80 Hrs" else if [New Contract] = "FD EX 10W ON / 4W OFF" then "Ex 10W/4W" else if [New Contract] = "FD EX 12 WEEKS ON 4 WEEKS OFF" then "Ex 12W/4W" else if [New Contract] = "FD EX 6 WEEKS ON 2 WEEKS OFF (85 HRS)" then "Ex 6W/2W (85 Hrs)" else if [New Contract] = "FD EX 6W ON/ 2W OFF" then "Ex 6W/2W" else if [New Contract] = "FD EX 2M ON / 4W OFF" then "Ex 2M/4W" else if [New Contract] = "FD EX 5M ON / 4W OFF" then "Ex 5M/4W" else if [New Contract] = "FD EX 3M ON / 4W OFF" then "Ex 3M/4W" else if [New Contract] = "FD EX 1M ON / 4W OFF" then "Ex 1M/4W" else if [New Contract] = "FD IN 11 W ON/ 2 W OFF" then "11W/2W" else if [New Contract] = "FD IN 1M ON / 4W OFF" then "1M/4W" else if [New Contract] = "FD IN 2M ON / 4W OFF" then "2M/4W" else if [New Contract] = "FD IN 3M ON / 4W OFF" then "3M/4W" else if [New Contract] = "FD IN 40 HOURS" then "40 Hrs" else if [New Contract] = "FD IN 5M ON / 4W OFF" then "5M/4W" else if [New Contract] = "FD IN 60 HOURS" then "60 Hrs" else if [New Contract] = "IN COCKPIT CREW ON 8 DAYS OFF CONTRACT" then "8 Day OFF" else if [New Contract] = "22 DAYS PL,FD EX 8 WEEK ON 3 WEEKS OFF" then "Ex 22 PL" else if [New Contract] = "FD EX 8 WEEK ON 3 WEEKS OFF" then "Ex 8W/3W" else if [New Contract] = "FE EX 46 DAYS ON 18 DAYS OFF" then "Ex 46D/18D" else null), #"Added Custom6" = Table.AddColumn(#"Added Custom5", "PL/VAC", each if [Short Contract] = "84 PL" then 84 else if [Short Contract] = "22 PL" then 22 else if [Short Contract] = "42 PL" then 42 else if [Short Contract] = "8 Day OFF" then 18 else if [Short Contract] = "80 Hrs" then 18 else if [Short Contract] = "Ex 10W/4W" then 104 else if [Short Contract] = "Ex 12W/4W" then 91 else if [Short Contract] = "Ex 6W/2W (85 Hrs)" then 91 else if [Short Contract] = "Ex 6W/2W" then 91 else if [Short Contract] = "Ex 1M/4W" then 176 else if [Short Contract] = "11W/2W" then 56 else if [Short Contract] = "1M/4W" then 176 else if [Short Contract] = "2M/4W" then 115 else if [Short Contract] = "3M/4W" then 86 else if [Short Contract] = "40 Hrs" then 197 else if [Short Contract] = "5M/4W" then 57 else if [Short Contract] = "60 Hrs" then 113 else if [Short Contract] = "8 Day OFF" then 18 else if [Short Contract] = "Ex 22 PL" then 22 else if [Short Contract] = "Ex 8W/3W" then 91 else if [Short Contract] = "Ex 46D/18D" then 102 else null), #"Added Custom7" = Table.AddColumn(#"Added Custom6", "CL", each if [Short Contract] = "84 PL" then 6 else if [Short Contract] = "22 PL" then 6 else if [Short Contract] = "42 PL" then 6 else if [Short Contract] = "8 Day OFF" then 6 else if [Short Contract] = "80 Hrs" then 12 else if [Short Contract] = "Ex 10W/4W" then 0 else if [Short Contract] = "Ex 12W/4W" then 0 else if [Short Contract] = "Ex 6W/2W (85 Hrs)" then 0 else if [Short Contract] = "Ex 6W/2W" then 0 else if [Short Contract] = "Ex 2M/4W" then 0 else if [Short Contract] = "Ex 5M/4W" then 0 else if [Short Contract] = "Ex 3M/4W" then 0 else if [Short Contract] = "Ex 1M/4W" then 0 else if [Short Contract] = "11W/2W" then 8 else if [Short Contract] = "1M/4W" then 6 else if [Short Contract] = "2M/4W" then 6 else if [Short Contract] = "3M/4W" then 6 else if [Short Contract] = "40 Hrs" then 12 else if [Short Contract] = "5M/4W" then 6 else if [Short Contract] = "60 Hrs" then 12 else if [Short Contract] = "8 Day OFF" then 6 else if [Short Contract] = "Ex 22 PL" then 0 else if [Short Contract] = "Ex 8W/3W" then 0 else if [Short Contract] = "Ex 46D/18D" then 0 else null), #"Added Custom8" = Table.AddColumn(#"Added Custom7", "SL", each if [Short Contract] = "84 PL" then 12 else if [Short Contract] = "22 PL" then 12 else if [Short Contract] = "42 PL" then 12 else if [Short Contract] = "8 Day OFF" then 6 else if [Short Contract] = "80 Hrs" then 12 else if [Short Contract] = "Ex 10W/4W" then 12 else if [Short Contract] = "Ex 12W/4W" then 12 else if [Short Contract] = "Ex 6W/2W (85 Hrs)" then 12 else if [Short Contract] = "Ex 6W/2W" then 12 else if [Short Contract] = "Ex 2M/4W" then 12 else if [Short Contract] = "Ex 5M/4W" then 12 else if [Short Contract] = "Ex 3M/4W" then 12 else if [Short Contract] = "Ex 1M/4W" then 12 else if [Short Contract] = "11W/2W" then 12 else if [Short Contract] = "1M/4W" then 6 else if [Short Contract] = "2M/4W" then 6 else if [Short Contract] = "3M/4W" then 6 else if [Short Contract] = "40 Hrs" then 6 else if [Short Contract] = "5M/4W" then 12 else if [Short Contract] = "60 Hrs" then 6 else if [Short Contract] = "8 Day OFF" then 6 else if [Short Contract] = "Ex 22 PL" then 12 else if [Short Contract] = "Ex 8W/3W" then 12 else if [Short Contract] = "Ex 46D/18D" then 12 else null), #"Added Custom9" = Table.AddColumn(#"Added Custom8", "OFF", each (365-[#"PL/VAC"])/7), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom9",{{"OFF", Int64.Type}}), #"Added Custom10" = Table.AddColumn(#"Changed Type", "Available Days", each 365-([#"PL/VAC"]+[CL]+[SL]+[OFF])), #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom10",{{"Available Days", Int64.Type}}), #"Added Custom11" = Table.AddColumn(#"Changed Type2", "Rank1", each if Text.Contains([Rank], "CP") then "Captain" else if Text.Contains([Rank], "FO") then "First Officer" else null), #"Added Custom12" = Table.AddColumn(#"Added Custom11", "As on Date 1", each if(Date.StartOfMonth([As_on_Date])=[As_on_Date]) then [As_on_Date] else ""), #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom12",{{"As on Date 1", type date}}), #"Added Custom13" = Table.AddColumn(#"Changed Type3", "Previous_month", each Date.StartOfMonth((Date.AddMonths([As on Date 1], -1)))), #"Duplicated Column" = Table.DuplicateColumn(#"Added Custom13", "IGA", "IGA - Copy"), #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "As on Date 1", "As on Date 1 - Copy"), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicated Column1", {{"IGA - Copy", type text}, {"As on Date 1 - Copy", type text}}, "en-IN"),{"IGA - Copy", "As on Date 1 - Copy"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"LookupKey"), #"Duplicated Column2" = Table.DuplicateColumn(#"Merged Columns", "IGA", "IGA - Copy"), #"Duplicated Column3" = Table.DuplicateColumn(#"Duplicated Column2", "Previous_month", "Previous_month - Copy"), #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicated Column3", {{"IGA - Copy", type text}, {"Previous_month - Copy", type text}}, "en-IN"),{"IGA - Copy", "Previous_month - Copy"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"LookupKeyPrev") in #"Merged Columns1"and this is the custom query
Select distinct tab1.[IGA],tab1.[Date],tab2.[DOJ],tab1.[Name],tab1.[Rank],tab1.[Contract], tab1.[Status],tab1.[CrewBase],tab1.[TrainingQualfication],tab1.[Nationality] from [MPPFDA].[dbo].[Old_headcount_Pilot] as tab1 left join [MPPFDA].[dbo].[pilotheadcount_datestamp] as tab2 on tab1.IGA=tab2.IGA where [Date] > '2018-01-01' order by DateAnonymous
As I mentioned earlier, create a new query and paste your SQL query in the bottom box.
Now go to the advanced editor and copy the below part.
Source = Sql.Database("SQLMPP-PRD-LSNR,53403", "Demo", [Query="SELECT ..........."]),Then paste it in your old query file.
Since we are mentioning the database name in the above step, you can skip your few steps
We don't need this #"Databasename" = Source{[Name="MPPFDA"]}[Data], dbo_YourTableName = #"Databasename"{[Schema="dbo",Item="pilotheadcount_datestamp"]}[Data],and start from below
#"Added Custom" = Table.AddColumn(Source,
Make sure you have a copy of the original file.
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂