Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

changing the source to SQL table from CSV file

‎05-08-2020 10:41 PM

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 new to power BI, need help. I am enclosing the M code in which I am trying to change the source.

 

 

 

 

let
    Source = Csv.Document(File.Contents("C:\Users\utsav.dhar\Desktop\pilotheadcountdata.csv"),[Delimiter=",", Columns=35, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"IGA", Int64.Type}, {"DOJ", type date}, {"Rank", type text}, {"CrewBase", type text}, {"Contract", type text}, {"STATUS", type text}, {"TrainingQualfication", type text}, {"Nationality", type text}, {"CAT3", type text}, {"LVTO", type text}, {"KTM", type text}, {"Short", type text}, {"ShortCityPair", type text}, {"DEDCleared", type text}, {"IXJ", type text}, {"IXZ", type text}, {"SXR", type text}, {"Gulf", type text}, {"South", type text}, {"ETOPS", type text}, {"Passport", type text}, {"Name", type text}, {"ExpCatgryCP", type text}, {"ExpCatgryFO", type text}, {"KtmSimExpiry", type text}, {"TreExpiry", type text}, {"EtpExpiry", type text}, {"KTMExpiry", type text}, {"IxjExpiry", type text}, {"SxrExpiry", type text}, {"RestrictedByOpsForINT", type text}, {"PASSPORTforVISA", type text}, {"AnnexB", type text}, {"NeoQualified", type text}, {"As_on_Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "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

 

 

 

 

 

 

Labels:
Message 1 of 6
2,100 Views
0
1 ACCEPTED SOLUTION
nandukrishnavs
Super User
Super User

‎05-08-2020 11:09 PM

@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 🙂


Regards,
Nandu Krishna

View solution in original post

Message 2 of 6
2,085 Views
5 REPLIES 5
nandukrishnavs
Super User
Super User

‎05-08-2020 11:09 PM

@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 🙂


Regards,
Nandu Krishna

Message 2 of 6
2,086 Views
Anonymous
Not applicable
In response to nandukrishnavs

‎05-08-2020 11:17 PM

Thank you so much, worked like a charm 🙂

Message 3 of 6
2,081 Views
0
Anonymous
Not applicable
In response to Anonymous

‎05-16-2020 12:15 AM

@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 Date
Message 4 of 6
1,913 Views
0
nandukrishnavs
Super User
Super User
In response to Anonymous

‎05-16-2020 01:07 AM

@Anonymous 

 

Your final query might be similar to this.

let
  Source = Sql.Database(
    "SQLMPP-PRD-LSNR,53403", 
    "MPPFDA", 
    [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 Date"]
  ),
  #"Added Custom" = Table.AddColumn(
    Source, 
    "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"



Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂


Regards,
Nandu Krishna

Message 6 of 6
1,900 Views
0
nandukrishnavs
Super User
Super User
In response to Anonymous

‎05-16-2020 12:59 AM

@Anonymous 

 

As I mentioned earlier, create a new query and paste your SQL query in the bottom box.

Capture.JPG

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 🙂


Regards,
Nandu Krishna

Message 5 of 6
1,904 Views
0

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

View All