Forum Discussion
Syndicate_Admin
Administrator
AdministratorRango avanzado en Power Query
¡Hola! ¿Cómo puedo hacer esto en el Editor de Power Query? Necesito crear un ranking por mes de ciertos valores. Aquí está la tabla que estoy tratando de recrear. Necesito calcular la columna Ranking...
Descargar archivo PBIX de ejemplo
Esta consulta lo hace
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJU0lEyNDBVitVBEzJGF7LAUGQJETFC1maOKWSBLmSJKWKmFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Index = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Index", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index.1", 0, 1, Int64.Type), #"Sorted Rows" = Table.Sort(#"Added Index",{{"Date", Order.Ascending}, {"Index", Order.Descending}}), #"Grouped Rows" = Table.Group(Table.Buffer(#"Sorted Rows"), {"Date"}, {{"Count", each _, type table [Date=nullable date, Index=nullable number, Index.1=number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index.2", 1, 1, Int64.Type)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Index", "Index.1", "Index.2"}, {"Index", "Index.1", "Index.2"}), #"Sorted Rows1" = Table.Sort(#"Expanded Custom",{{"Date", Order.Ascending}, {"Index.1", Order.Ascending}}), #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows1",{"Index.1"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Index.2", "Ranking"}}) in #"Renamed Columns"Saludos
Phil
Syndicate_AdminAdministrator
Administrator
Descargar archivo PBIX de ejemplo
Esta consulta lo hace
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJU0lEyNDBVitVBEzJGF7LAUGQJETFC1maOKWSBLmSJKWKmFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Index = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Index", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index.1", 0, 1, Int64.Type),
#"Sorted Rows" = Table.Sort(#"Added Index",{{"Date", Order.Ascending}, {"Index", Order.Descending}}),
#"Grouped Rows" = Table.Group(Table.Buffer(#"Sorted Rows"), {"Date"}, {{"Count", each _, type table [Date=nullable date, Index=nullable number, Index.1=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index.2", 1, 1, Int64.Type)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Index", "Index.1", "Index.2"}, {"Index", "Index.1", "Index.2"}),
#"Sorted Rows1" = Table.Sort(#"Expanded Custom",{{"Date", Order.Ascending}, {"Index.1", Order.Ascending}}),
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows1",{"Index.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Index.2", "Ranking"}})
in
#"Renamed Columns"
Saludos
Phil
Syndicate_AdminAdministrator
AdministratorHola Felipe
¡Muchas gracias por la respuesta! Intenté parchear esa consulta en mi fuente original y no obtuve ningún error de sintaxis, así que eso es bueno. Sin embargo, una vez que intentó ejecutar la consulta, obtengo un error que indica:
Expresión.Error: No se encontró la columna 'Index.2' de la tabla.
Detalles:
Índice.2
Aquí está la consulta después de que el espacio es su consulta
let
Source = SharePoint.Files("XXX", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "XXX")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", let latest = List.Max(#"Filtered Rows"[Date created]) in each [Date created] = latest),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows1", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (6)", each #"Transform File (6)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (6)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (6)", Table.ColumnNames(#"Transform File (6)"(#"Sample File (6)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Census#", Int64.Type}, {"DateTY", type text}, {"PropSupTY", Int64.Type}, {"PropDemTY", Int64.Type}, {"PropRevTY", Int64.Type}, {"CompSupTY", Int64.Type}, {"CompDemTY", Int64.Type}, {"CompRevTY", Int64.Type}, {"OccRankTY", type text}, {"ADRRankTY", type text}, {"RevParRankTY", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"DateTY", "Census#", "PropSupTY", "PropDemTY", "PropRevTY", "CompSupTY", "CompDemTY", "CompRevTY", "OccRankTY", "ADRRankTY", "RevParRankTY"}),
#"Inserted Suffix" = Table.AddColumn(#"Reordered Columns", "Suffix", each [DateTY] & "01", type text),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted Suffix",{{"Suffix", type date}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Changed Type1",{"Suffix", "DateTY", "Census#", "PropSupTY", "PropDemTY", "PropRevTY", "CompSupTY", "CompDemTY", "CompRevTY", "OccRankTY", "ADRRankTY", "RevParRankTY"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns1",{{"Suffix", "Date"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"DateTY"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Census#", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type2", "Index", each ([PropRevTY]/[PropSupTY])/([CompRevTY]/[CompSupTY])),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom",{{"Index", type number}}),
#"Changed Type4" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Index", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index.1", 0, 1, Int64.Type),
#"Sorted Rows" = Table.Sort(#"Added Index",{{"Date", Order.Ascending}, {"Index", Order.Descending}}),
#"Grouped Rows" = Table.Group(Table.Buffer(#"Sorted Rows"), {"Date"}, {{"Count", each _, type table [Date=nullable date, Index=nullable number, Index.1=number]}}),
#"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index.2", 1, 1, Int64.Type)),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Count"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Index", "Index.1", "Index.2"}, {"Index", "Index.1", "Index.2"}),
#"Sorted Rows1" = Table.Sort(#"Expanded Custom",{{"Date", Order.Ascending}, {"Index.1", Order.Ascending}}),
#"Removed Columns3" = Table.RemoveColumns(#"Sorted Rows1",{"Index.1"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns1",{{"Index.2", "Ranking"}})
in
#"Renamed Columns2"- Syndicate_Admin
Hay @jeamps01
¿Puede enviarme los datos que está utilizando y una captura de pantalla de la tabla en Power Query antes de agregar mis pasos de consulta?
Mi consulta utiliza nombres de columna específicos de las columnas de los datos ficticios que configuré. La consulta utiliza nombres de columna que se encuentran en los datos.
Necesita replicar mis pasos para llevar a cabo las transformaciones en sus datos / columnas. El error que está recibiendo es porque mis datos ficticios tienen una columna llamada Index.2, la suya no.
Si puedo ver qué datos y nombres de columna tiene, puedo intentar modificar mi código para que coincida con los nombres de sus columnas.
Saludos
Phil