cambiando la fuente a la tabla SQL desde el archivo CSV

Un usuario Pregunto ✅

Anónimo

Hola,

Estoy tratando de editar el código M para cambiar la fuente de csv directamente desde el servidor SQL. pero estoy perdiendo mis columnas personalizadas. Si solo cambio la fuente, está arrojando un error. Soy bastante nuevo en Power BI, necesito ayuda. Adjunto el código M en el que intento cambiar la fuente.

let
    Source = Csv.Document(File.Contents("C:Usersutsav.dharDesktoppilotheadcountdata.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

nandukrishnavs

@Anónimo

Puede crear otra consulta para obtener los datos de la tabla SQL. Asegúrese de que todas las columnas también estén disponibles en SQL. Los nombres de las columnas deben ser iguales. Luego vaya al editor avanzado y copie PowerQuery. Luego reemplace la parte inicial de su consulta existente.

Ejemplo

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

¿Respondí tu pregunta? ¡Marque mi publicación como una solución!
Apreciar con felicitaciones
🙂

nandukrishnavs

@Anónimo

Puede crear otra consulta para obtener los datos de la tabla SQL. Asegúrese de que todas las columnas también estén disponibles en SQL. Los nombres de las columnas deben ser iguales. Luego vaya al editor avanzado y copie PowerQuery. Luego reemplace la parte inicial de su consulta existente.

Ejemplo

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

¿Respondí tu pregunta? ¡Marque mi publicación como una solución!
Apreciar con felicitaciones
🙂

Anónimo

En respuesta a nandukrishnavs

Muchas gracias, funcionó a las mil maravillas 🙂

Anónimo

En respuesta a Anónimo

@nandukrishnavs Una consulta más. En el código M anterior, estoy tratando de ingresar una consulta SQL personalizada como se adjunta. Me está lanzando un error. Puede usted ayudar.

Este es el mcode que estoy intentando editar

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"

y esta es la consulta personalizada

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

nandukrishnavs

En respuesta a Anónimo

@Anónimo

Su consulta final puede ser similar a esta.

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"

¿Respondí tu pregunta? ¡Marque mi publicación como una solución!
Apreciar con felicitaciones
🙂

nandukrishnavs

En respuesta a Anónimo

@Anónimo

Como mencioné anteriormente, cree una nueva consulta y pegue su consulta SQL en el cuadro inferior.

Capture.JPG

Ahora vaya al editor avanzado y copie la parte siguiente.

 Source = Sql.Database("SQLMPP-PRD-LSNR,53403", "Demo", [Query="SELECT ..........."]),

Luego péguelo en su antiguo archivo de consulta.

Como mencionamos el nombre de la base de datos en el paso anterior, puede omitir los pocos pasos

We don't need this
#"Databasename" = Source{[Name="MPPFDA"]}[Data],
dbo_YourTableName = #"Databasename"{[Schema="dbo",Item="pilotheadcount_datestamp"]}[Data],

y empezar desde abajo

# «Agregado personalizado» = Table.AddColumn (Fuente,

Asegúrese de tener una copia del archivo original.

¿Respondí tu pregunta? ¡Marque mi publicación como una solución!
Apreciar con felicitaciones
🙂

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *