Quitar totales de resumen de una consulta

[Syndicate_Admin]

Tarde todos,

Tengo una consulta de una tabla de datos que contiene los valores de los elementos de línea individuales, junto con los valores de resumen de los elementos de línea de nivel inferior de una tabla. Vea la captura de pantalla a continuación.

La línea 1 es un resumen de todos los importes de las tarifas en WBS1=21064

Live 4 es un resumen de todos los montos de tarifas en 21064 y WBS2 = 10, etc. etc.

Idealmente, me gustaría eliminar las filas de resumen y solo dejar los montos de tarifa de nivel más bajo para que BI pueda resumirse en las imágenes, etc.

Los datos tienen algunas líneas donde el nivel más bajo está en WBS1, otros con WBS1 y WBS2, y otros donde hay los tres niveles.

Cualquier ayuda sobre cómo limpiar estos datos al nivel más bajo disponible de datos sería apreciada.

¡Bien!

[Syndicate_Admin]

RESPUESTA CORRECTA

He encontrado un error en mi solución anterior cuando Max WBS Nesting basado en WBS.1 no es el mismo para todos los WBS.1 secundarios.

Por eso he preparado una nueva versión, pero mejor.

Después de un poco de limpieza en los datos ("null" como texto reemplazado por null)

Paso 1. Agregue una nueva columna ParentPath.

Paso 2. Agregar nueva columna CurrentPath

Paso 3. Agregar nueva columna isParent (comprueba si la ruta actual se puede encontrar en ParentPathList)

Con esta tabla, puede combinar consultas para recibir folumn isParent y filtrar Padres.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZbRboMgFIZfhZhd2gVQYL12y27W2KS9WZpesJUYN4sN2j1Pn6VPNmzaTJh1oF0MEQX5Pw78B1erAEMM7yEKwmCuyg/xXgO7LvdF8et2PNwdD10XQHEIoR4S6l5NQcE6vCaz5NUnSNVGqLFKzeeRu9I8AUlZqk0ueZ2XcqAo1iUeIzoTos5lVgEuN1eArpKcaNgPTKQL+S+Yxf5tcm63m5wBY13oGMCkrGrwVNX5ljcgA2PEbovwKKo8k5d3ovKNyMMFB5s42Kq7egNMGQ5RpwE7FNoTBhOQpD5KiLaU0MkPfkrPQgrFCx1kWSuuO8645JnYClm7O9JAemhbM/LE0e2Z4tvxFKRNQQdSLPa7XaluEggEfRlmYKl4Lg2X/ZkaKTRTI4pHybaewIv4EkX7jHCjaPyOiDdF6hN8oC1gzhs7+sDH1hdzk5AYR57j5CJ/KWKfrlM3qdgnheB27JpB4949kxK9SBF4FVyBs08GxNCWZP6SN8tcwP6d6I1yN8yYvAUYNQFIb9a8AjAoXSDLNrTXNin1X2kGTbdQR7cwny0c2VuY6Wy7/gY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Contract ID" = _t, #"Contract Name" = _t, WBS.1 = _t, WBS.2 = _t, WBS.3 = _t, WBS.4 = _t, #" Fee " = _t, #"WBS Nesting" = _t, #"Sort Order" = _t]),
    Hierarchy = Table.SelectColumns(Source,{"Contract Name", "WBS.1", "WBS.2", "WBS.3", "WBS.4", "WBS Nesting"}),
    #"Changed Type" = Table.TransformColumnTypes(Hierarchy,{{"Contract Name", type text}, {"WBS.1", type text}, {"WBS.2", type text}, {"WBS.3", type text}, {"WBS.4", type text}, {"WBS Nesting", Int64.Type}}),
    #"Cleaning the data" = Table.ReplaceValue(#"Changed Type","null",null,Replacer.ReplaceValue,Table.ColumnNames(#"Changed Type")),
    #"Add ParentPath" = Table.AddColumn(#"Cleaning the data", "ParentPath", each
if [WBS Nesting] = 4 then
Text.Combine(List.Select({[Contract Name],[WBS.1],[WBS.2],[WBS.3]},each _<> "" and _ <> null)," & ")
else if [WBS Nesting] = 3 then
Text.Combine(List.Select({[Contract Name],[WBS.1],[WBS.2]},each _<> "" and _ <> null)," & ")
else if [WBS Nesting] = 2 then
Text.Combine(List.Select({[Contract Name],[WBS.1]},each _<> "" and _ <> null)," & ")
else [Contract Name]),
    #"Add CurrentPath" = Table.AddColumn(#"Add ParentPath", "CurrentPath", each
    if [Contract Name] = [WBS.1] then [Contract Name] else Text.Combine(List.Select({[Contract Name],[WBS.1],[WBS.2],[WBS.3],[WBS.4]},each _<> "" and _ <> null)," & ")
),
    #"Add isParent" = Table.AddColumn(#"Add CurrentPath", "isParent", each List.Contains(
    Table.ToList(Table.Distinct(Table.SelectColumns(#"Add CurrentPath","ParentPath"))),
 [CurrentPath]))
in
    #"Add isParent"

[Syndicate_Admin]

Basado en los datos de ejemplo que ha proporcionado (tabla SampleData).
Paso 1. Crear tabla MaxWBSNesting

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZbRboMgFIZfhZhd2gVQYL12y27W2KS9WZpesJUYN4sN2j1Pn6VPNmzaTJh1oF0MEQX5Pw78B1erAEMM7yEKwmCuyg/xXgO7LvdF8et2PNwdD10XQHEIoR4S6l5NQcE6vCaz5NUnSNVGqLFKzeeRu9I8AUlZqk0ueZ2XcqAo1iUeIzoTos5lVgEuN1eArpKcaNgPTKQL+S+Yxf5tcm63m5wBY13oGMCkrGrwVNX5ljcgA2PEbovwKKo8k5d3ovKNyMMFB5s42Kq7egNMGQ5RpwE7FNoTBhOQpD5KiLaU0MkPfkrPQgrFCx1kWSuuO8645JnYClm7O9JAemhbM/LE0e2Z4tvxFKRNQQdSLPa7XaluEggEfRlmYKl4Lg2X/ZkaKTRTI4pHybaewIv4EkX7jHCjaPyOiDdF6hN8oC1gzhs7+sDH1hdzk5AYR57j5CJ/KWKfrlM3qdgnheB27JpB4949kxK9SBF4FVyBs08GxNCWZP6SN8tcwP6d6I1yN8yYvAUYNQFIb9a8AjAoXSDLNrTXNin1X2kGTbdQR7cwny0c2VuY6Wy7/gY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Contract ID" = _t, #"Contract Name" = _t, WBS.1 = _t, WBS.2 = _t, WBS.3 = _t, WBS.4 = _t, #" Fee " = _t, #"WBS Nesting" = _t, #"Sort Order" = _t]),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"WBS.1", "WBS Nesting"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"WBS.1", type text}, {"WBS Nesting", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"WBS.1"}, {{"MaxNesting", each List.Max([WBS Nesting]), type nullable number}})
in
    #"Grouped Rows"

Paso 2. Combine esta tabla con su origen en WBS.1 y expanda MaxNesting Coulmn a su origen.

Paso 3. Crear columna personalizada

if [Contract Name] = [WBS.1] o [WBS Nesting] <> [MaxNesting] then "REMOVE" else "KEEP"

Resultado final:

Filtrar las filas 🙂 REMOVE

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZbRboMgFIZfhZhd2gVQYL12y27W2KS9WZpesJUYN4sN2j1Pn6VPNmzaTJh1oF0MEQX5Pw78B1erAEMM7yEKwmCuyg/xXgO7LvdF8et2PNwdD10XQHEIoR4S6l5NQcE6vCaz5NUnSNVGqLFKzeeRu9I8AUlZqk0ueZ2XcqAo1iUeIzoTos5lVgEuN1eArpKcaNgPTKQL+S+Yxf5tcm63m5wBY13oGMCkrGrwVNX5ljcgA2PEbovwKKo8k5d3ovKNyMMFB5s42Kq7egNMGQ5RpwE7FNoTBhOQpD5KiLaU0MkPfkrPQgrFCx1kWSuuO8645JnYClm7O9JAemhbM/LE0e2Z4tvxFKRNQQdSLPa7XaluEggEfRlmYKl4Lg2X/ZkaKTRTI4pHybaewIv4EkX7jHCjaPyOiDdF6hN8oC1gzhs7+sDH1hdzk5AYR57j5CJ/KWKfrlM3qdgnheB27JpB4949kxK9SBF4FVyBs08GxNCWZP6SN8tcwP6d6I1yN8yYvAUYNQFIb9a8AjAoXSDLNrTXNin1X2kGTbdQR7cwny0c2VuY6Wy7/gY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Contract ID" = _t, #"Contract Name" = _t, WBS.1 = _t, WBS.2 = _t, WBS.3 = _t, WBS.4 = _t, #" Fee " = _t, #"WBS Nesting" = _t, #"Sort Order" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Contract ID", type date}, {"Contract Name", type text}, {"WBS.1", type text}, {"WBS.2", type text}, {"WBS.3", type text}, {"WBS.4", type text}, {" Fee ", type text}, {"WBS Nesting", Int64.Type}, {"Sort Order", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"WBS.1"}, MAXWBSNesting, {"WBS.1"}, "MAXWBSNesting", JoinKind.LeftOuter),
    #"Expanded MAXWBSNesting" = Table.ExpandTableColumn(#"Merged Queries", "MAXWBSNesting", {"MaxNesting"}, {"MaxNesting"}),
    #"Added Custom" = Table.AddColumn(#"Expanded MAXWBSNesting", "Keep or Remove", each if [Contract Name] = [WBS.1] or [WBS Nesting] <> [MaxNesting] then "REMOVE" else "KEEP")
in
    #"Added Custom"

[Syndicate_Admin]

Sin filtros, fue capaz de lograr con transformaciones y algunas sentencias If.

[Syndicate_Admin]

Hola

No veo ningún patrón allí en absoluto. Así que no sé qué condición de filtro se puede aplicar para eliminar las filas de color gris.

[Syndicate_Admin]

Hola

Similar a la publicación original; Tengo una jerarquía WBS con filas de resumen de los niveles inferiores. No he tenido éxito en el uso de la técnica anterior para eliminar correctamente las filas de resumen y podría usar algo de ayuda sobre cómo modificarlo. Datos de muestra a continuación, tengo cientos de proyectos con diferentes niveles de jerarquía WBS y nomenclatura inconsistente ni ninguna tabla de relación padre hijo.

Datos de muestra a continuación. Necesito eliminar las filas resaltadas ya que esas son sumas de los niños. Cualquier ayuda es apreciada, gracias.

ID del contrato	Nombre del contrato	EDT.1	WBS.2	EDT.3	EDT.4	Honorario	Anidamiento WBS	Orden de clasificación
2020.01	Proyecto 1	Proyecto 1	nulo	nulo	nulo	$ 14.000,00	0	1
2020.01	Proyecto 1	Orden de tareas 1	nulo	nulo	nulo	$ 14.000,00	1	3
2020.01	Proyecto 1	Orden de tareas 1	Coordinación de PC	nulo	nulo	$ 14.000,00	2	4
2020.01	Proyecto 1	Orden de tareas 1	Coordinación de PC	Reuniones y coordinación	nulo	$ 7.000,00	3	5
2020.01	Proyecto 1	Orden de tareas 1	Coordinación de PC	Reuniones y coordinación	Coordinación de subreuniones	$ 7.000,00	4	6
2020.01	Proyecto 1	Orden de tareas 1	Coordinación de PC	Estimación de costos	nulo	$ 7.000,00	3	7
2020.01	Proyecto 1	Orden de tareas 1	Coordinación de PC	Estimación de costos	Estimaciones de diseño	$ 7.000,00	4	8
2022.01	Proyecto 2	Proyecto 2	nulo	nulo	nulo	$ 972.100,00	0	1
2022.01	Proyecto 2	Orden de tarea 1 - CO	nulo	nulo	nulo	$ 162.100,00	1	2
2022.01	Proyecto 2	Orden de tarea 1 - CO	Gestión General de Contratos	nulo	nulo	$ 800,00	2	3
2022.01	Proyecto 2	Orden de tarea 1 - CO	Gestión de programas	nulo	nulo	$ 500,00	2	6
2022.01	Proyecto 2	Orden de tarea 1 - CO	Apoyo al programa	nulo	nulo	$ 800,00	2	10
2022.01	Proyecto 2	Orden de tarea 1 - CO	Formación PM	nulo	nulo	$ 60.000,00	2	14
2022.01	Proyecto 2	Orden de tarea 1 - CO	Formación PM	Entrenamiento PM Nivel 1	nulo	$ 60.000,00	3	15
2022.01	Proyecto 2	Orden de tarea 1 - CO	Soporte de PMO	nulo	nulo	$ 100.000,00	2	22
2022.01	Proyecto 2	Orden de tareas 2	nulo	nulo	nulo	$ 95.500,00	1	35
2022.01	Proyecto 2	Orden de tareas 3	nulo	nulo	nulo	$ 54.000,00	1	39
2022.01	Proyecto 2	Orden de tareas 4	nulo	nulo	nulo	$ 200.000,00	1	44
2022.01	Proyecto 2	TO5 - Programa de 3 años	nulo	nulo	nulo	$ 90.000,00	1	47
2022.01	Proyecto 2	TO5 - Programa de 3 años	Gestión General de Contratos	nulo	nulo	$ 4.000,00	2	49
2022.01	Proyecto 2	TO5 - Programa de 3 años	Gestión de programas	nulo	nulo	$ 76.000,00	2	53
2022.01	Proyecto 2	TO5 - Programa de 3 años	Formación PM	nulo	nulo	$ 10.000,00	2	62
2022.01	Proyecto 2	A6	nulo	nulo	nulo	$ 70.500,00	1	65
2022.01	Proyecto 2	Orden de tareas 7	nulo	nulo	nulo	$ 300.000,00	1	70

[Syndicate_Admin]

Hola

Comparta algunos datos, explique la pregunta y muestre el resultado esperado.

[Syndicate_Admin]

@rdraytonNBRS ¿Alguna vez resolviste esto? Tengo un problema similar y me encuentro con los mismos problemas

[Syndicate_Admin]

@rdraytonNBRS ,

Puede consultar mi código M para obtener más detalles sobre cómo eliminar el resumen.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBRCgAhCETv4ncfjmbsniW6/zVWra/FICacN6I4JzGokYBH93+/14yZVvtB9xIXCIGgLqZ241HrHUNchlSTN0fKUwUkAiLpVAM2D6d7/y0Q63PZrwejvkw/eCRdHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ContractNumber = _t, WBS1 = _t, WBS2 = _t, WBS3 = _t, Fee = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ContractNumber", type text}, {"WBS1", Int64.Type}, {"WBS2", Int64.Type}, {"WBS3", Int64.Type}, {"Fee", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Fee", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ContractNumber", "WBS1", "WBS2"}, {{"Count", each _, type table [ContractNumber=nullable text, WBS1=nullable number, WBS2=nullable number, WBS3=nullable number, Fee=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"WBS3", "Fee", "Index"}, {"Custom.WBS3", "Custom.Fee", "Custom.Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom.WBS3", "WBS3"}, {"Custom.Fee", "Fee"}, {"Custom.Index", "Index"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "0 remove and 1 keep", each let _Max =
List.Max(
let 
_ContractNumber = [ContractNumber],
_WBS1 = [WBS1],
_WBS2 = [WBS2]
in
Table.SelectRows(#"Renamed Columns",each _ContractNumber = [ContractNumber] and _WBS1 = [WBS1] and _WBS2 = [WBS2])[Index]
,
[Index])
in
if [WBS2] = null then 0 
else if _Max = 1 then 1 else if [Index] = 1 then 0 else 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([0 remove and 1 keep] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count", "Index", "0 remove and 1 keep"})
in
    #"Removed Columns"

El resultado es el siguiente.

Saludos
Rico Zhou

Si esta publicación ayuda, considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

[Syndicate_Admin]

Gracias por la ayuda. Creo que esta solución solo funcionará cuando solo tenga un valor WBS1 (como en mi ejemplo). Realmente necesito una solución que funcione si tengo VARIOS valores WBS1 en mi tabla. ¿Hay una adición simple aquí?

Vea una muestra de datos más grande a continuación.

[Syndicate_Admin]

@v-rzhou-msft - ¿Alguna otra idea sobre mi respuesta anterior? Agradecemos cualquier ayuda.