Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hola
Necesito ayuda sobre cómo capturar columnas con valores no NULL.
Supervisor1 =
SI EMPLOYEE10 ESTÁ EN BLANCO, ENTONCES EMPLOYEE9
elseif EMPLOYEE9 está en blanco y luego EMPLOYEE8
elseif EMPLOYEE8 está en blanco y luego EMPLOYEE7
elseif EMPLOYEE7 está en blanco y luego EMPLOYEE6
elseif EMPLOYEE6 está en blanco y luego EMPLOYEE5
elseif EMPLOYEE5 está en blanco y luego EMPLOYEE4
elseif EMPLOYEE4 está en blanco y luego EMPLOYEE3
elseif EMPLOYEE3 está en blanco y luego EMPLOYEE2
else EMPLEADO1
fin
fórmula para Supervisor2... adelante.....
He intentado anidado IF y SWITCH instrucciones, pero no pude conseguir que funcione. A continuación se muestra el enlace para la muestra pbix y Excel
https://drive.google.com/drive/folders/1WKeAb8NnIeHE931pKDNhPJMjHZuGZFpC?usp=sharing
Saludos
Verano
Solved! Go to Solution.
Hay @summer18 ,
Vaya al editor de consultas y use los siguientes códigos M:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZU9BagMxDPyK8DmXpC9otqWh5FDYkFCWPYhErEVsq8jrgPP6yhtoC4U5zCBpZjQMbu1W7gcHidBHnr3xd/EJXoQaxUSwRZ2N71ERntOFNEtaTlKFLqBel8XzFXb0O+1ruDHCiUNgjNmNq8Ft/iY+0OHsSdlSPqWYfo0cKux54TeEo91jCyexSpHTVOhueqvlTNBzlESL99N/b8Ob0iRa4cBzyaaP9k/OCJ2W5vLhW70vOGGaWhmvnKETperG8Rs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RowID = _t, EMPLOYEE10 = _t, EMPLOYEE9 = _t, EMPLOYEE8 = _t, EMPLOYEE7 = _t, EMPLOYEE6 = _t, EMPLOYEE5 = _t, EMPLOYEE4 = _t, EMPLOYEE3 = _t, EMPLOYEE2 = _t, EMPLOYEE1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"RowID", Int64.Type}, {"EMPLOYEE10", type text}, {"EMPLOYEE9", type text}, {"EMPLOYEE8", type text}, {"EMPLOYEE7", type text}, {"EMPLOYEE6", type text}, {"EMPLOYEE5", type text}, {"EMPLOYEE4", type text}, {"EMPLOYEE3", type text}, {"EMPLOYEE2", type text}, {"EMPLOYEE1", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"RowID"}, "Attribute", "Value"),
#"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Attribute", Order.Ascending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each [Value] <> null and [Value] <> ""),
#"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute]), "Attribute", "Value"),
#"Renamed Columns" = Table.ColumnNames(#"Pivoted Column"),
#"Converted to Table" = Table.FromList(#"Renamed Columns", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each [Column1]),
List = Table.ToRows( Table.ReplaceValue(#"Added Custom","EMPLOYEE","Supervisor",Replacer.ReplaceText,{"Custom"})),
#"Renamed Columns1" = Table.RenameColumns(#"Pivoted Column",List)
in
#"Renamed Columns1"
Y verás:
Para el archivo .pbix relacionado, pls ver adjunto.
Saludos
Kelly
¿Respondí a su pregunta? Marque mi respuesta como una solución!
Hay @summer18 ,
Vaya al editor de consultas y use los siguientes códigos M:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZU9BagMxDPyK8DmXpC9otqWh5FDYkFCWPYhErEVsq8jrgPP6yhtoC4U5zCBpZjQMbu1W7gcHidBHnr3xd/EJXoQaxUSwRZ2N71ERntOFNEtaTlKFLqBel8XzFXb0O+1ruDHCiUNgjNmNq8Ft/iY+0OHsSdlSPqWYfo0cKux54TeEo91jCyexSpHTVOhueqvlTNBzlESL99N/b8Ob0iRa4cBzyaaP9k/OCJ2W5vLhW70vOGGaWhmvnKETperG8Rs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RowID = _t, EMPLOYEE10 = _t, EMPLOYEE9 = _t, EMPLOYEE8 = _t, EMPLOYEE7 = _t, EMPLOYEE6 = _t, EMPLOYEE5 = _t, EMPLOYEE4 = _t, EMPLOYEE3 = _t, EMPLOYEE2 = _t, EMPLOYEE1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"RowID", Int64.Type}, {"EMPLOYEE10", type text}, {"EMPLOYEE9", type text}, {"EMPLOYEE8", type text}, {"EMPLOYEE7", type text}, {"EMPLOYEE6", type text}, {"EMPLOYEE5", type text}, {"EMPLOYEE4", type text}, {"EMPLOYEE3", type text}, {"EMPLOYEE2", type text}, {"EMPLOYEE1", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"RowID"}, "Attribute", "Value"),
#"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Attribute", Order.Ascending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each [Value] <> null and [Value] <> ""),
#"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute]), "Attribute", "Value"),
#"Renamed Columns" = Table.ColumnNames(#"Pivoted Column"),
#"Converted to Table" = Table.FromList(#"Renamed Columns", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each [Column1]),
List = Table.ToRows( Table.ReplaceValue(#"Added Custom","EMPLOYEE","Supervisor",Replacer.ReplaceText,{"Custom"})),
#"Renamed Columns1" = Table.RenameColumns(#"Pivoted Column",List)
in
#"Renamed Columns1"
Y verás:
Para el archivo .pbix relacionado, pls ver adjunto.
Saludos
Kelly
¿Respondí a su pregunta? Marque mi respuesta como una solución!
Hay @summer18 ,
Encantados de ayudar.
Saludos
Kelly
¿Respondí a su pregunta? Marque mi respuesta como una solución!
@summer18 , una nueva columna como
Supervisor1 =
Switch(True(),
isblank([EMPLEADO10]), [EMPLEADO9],
isblank([EMPLEADO9]), [EMPLEADO8],
isblank([EMPLEADO8]), [EMPLEADO7],
Agregar otros
[EMPLEADO1]
)