hello.. thanks i solved in this way:
let
// Choose whether to refer directly to the table (query) or the result in the worksheet (Excel table).
// This is only for query speed reasons since the previous table only needs to be updated once or twice a day.
Origine = Excel.CurrentWorkbook(){[Name="competenza_riferimento"]}[Content],
// Origine = #"competenza-riferimento",
// Join with the mapping table
UnioneMapServizi = Table.NestedJoin(Origine, {"CodiceFiscale"}, MappingServizi, {"Cod_Fornitore"}, "MatchServizio", JoinKind.LeftOuter),
// Add the "ServizioFinale" column without expanding the subtable
AggiungiServizioFinale = Table.AddColumn(
UnioneMapServizi,
"ServizioFinale",
each
let
descr = [Descrizione], // Text to analyze
subtab = [MatchServizio], // Subtable with keywords and service
// Filters subtable rows based on the presence of keywords
// - if none of the keywords are found but the supplier (matching tax code) has a default service, assign that one;
// otherwise, search for service and beneficiary keywords.
righeMatch = Table.SelectRows(
subtab,
// Anonymous function that searches within the description field
(r) =>
(r[ParolaChiaveServizio] = null or Text.Contains(Text.Lower(descr), Text.Lower(r[ParolaChiaveServizio]))) and
(r[ParolaChiaveServizio2] = null or Text.Contains(Text.Lower(descr), Text.Lower(r[ParolaChiaveServizio2]))) and
(r[ParolaChiaveBeneficiario] = null or Text.Contains(Text.Lower(descr), Text.Lower(r[ParolaChiaveBeneficiario])))
)
in
if Table.IsEmpty(righeMatch) // If the result is empty, assign null
then null
else righeMatch{0}[Servizio], // Take the first valid service
type text
),
// Group by invoice
RaggruppaPerFattura = Table.Group(
AggiungiServizioFinale,
{"id-doc"},
{{"Dati", each _, type table}}
),
// Assign the service (the first valid one) to all invoice rows if present in at least one row; otherwise, remains null
PropagaServizioFattura = Table.AddColumn(
RaggruppaPerFattura,
"ServizioFattura",
each
let
ListaServizi = List.RemoveNulls(List.Distinct(Table.Column([Dati], "ServizioFinale")))
in
if List.Count(ListaServizi) > 0 then List.First(ListaServizi) else null,
type text
),
// Expand the "ServiziFattura" column
EspandiServizioFattura = Table.ExpandTableColumn(
Table.NestedJoin(
AggiungiServizioFinale,
"id-doc",
PropagaServizioFattura,
"id-doc",
"DatiServizio",
JoinKind.LeftOuter
),
"DatiServizio",
{"ServizioFattura"}
),
// Add the "BeneficiarioFinale" column without expanding the subtable
AggiungiBeneficiarioFinale = Table.AddColumn(
EspandiServizioFattura,
"Beneficiario",
each
let
descr = [Descrizione], // Text to analyze
subtab = [MatchServizio], // Subtable with keywords and beneficiary
// Filter subtable rows based on the presence of keywords
righeMatch = Table.SelectRows(
subtab,
(r) =>
(r[ParolaChiaveServizio] = null or Text.Contains(Text.Lower(descr), Text.Lower(r[ParolaChiaveServizio]))) and
(r[ParolaChiaveServizio2] = null or Text.Contains(Text.Lower(descr), Text.Lower(r[ParolaChiaveServizio2]))) and
(r[ParolaChiaveBeneficiario] = null or Text.Contains(Text.Lower(descr), Text.Lower(r[ParolaChiaveBeneficiario])))
)
in
if Table.IsEmpty(righeMatch)
then null
else righeMatch{0}[Beneficiario], // Take the first valid match
type text
),
// Remove auxiliary columns
#"Rimosse colonne1" = Table.RemoveColumns(AggiungiBeneficiarioFinale,{"MatchServizio", "ServizioFinale"}),
#"Rinominate colonne1" = Table.RenameColumns(#"Rimosse colonne1",{{"ServizioFattura", "Servizio"}}),
// Add a value of 1 to rows where a beneficiary is present, otherwise 0.
// The idea is to assign 1 to all rows of that invoice by grouping them.
Verifica_presenza_Ben = Table.AddColumn(#"Rinominate colonne1", "Presente_Beneficiario", each if [Beneficiario] <> null then 1 else 0),
// Group and assign 1 to all rows of the invoice where at least one row contains a beneficiary
RaggruppateRigheFattBen = Table.Group(Verifica_presenza_Ben, {"id-doc"}, {{"Pres_Ben_Fattura", each List.Max([Presente_Beneficiario]), type number}, {"table", each _, type table [id_doc_r=text, Idx=number, #"id-doc"=text, SommaRighe=number, CodiceFiscale=text, Fornitore=text, TipoDoc=text, DataDoc=datetime, NumeroDoc=text, ImportoTotaleDocumento=number, Causale=nullable text, CodiceCIG=nullable text, NumeroLinea=number, Descrizione=text, #"RigaFT-NC"=number, StatoVerifica=text, MeseCompetenza=text, AnnoCompetenza=text, Servizio=nullable text, Beneficiario=text, Presente_Beneficiario=number]}}),
// Expand the table, order columns, and remove "Presente_Beneficiario" column (which refers to a single row), keeping "Pres_Ben_Fattura"
EspandiBenFattura = Table.ExpandTableColumn(RaggruppateRigheFattBen, "table", {"id_doc_r", "Idx", "SommaRighe", "CodiceFiscale", "Fornitore", "TipoDoc", "DataDoc", "NumeroDoc", "ImportoTotaleDocumento", "Causale", "CodiceCIG", "NumeroLinea", "Descrizione", "RigaFT-NC", "StatoVerifica", "MeseCompetenza", "AnnoCompetenza", "Servizio", "Beneficiario", "Presente_Beneficiario"}, {"id_doc_r", "Idx", "SommaRighe", "CodiceFiscale", "Fornitore", "TipoDoc", "DataDoc", "NumeroDoc", "ImportoTotaleDocumento", "Causale", "CodiceCIG", "NumeroLinea", "Descrizione", "RigaFT-NC", "StatoVerifica", "MeseCompetenza", "AnnoCompetenza", "Servizio", "Beneficiario", "Presente_Beneficiario"}),
// Order and remove column
OrdinaColonne = Table.ReorderColumns(EspandiBenFattura,{"id-doc", "id_doc_r", "Idx", "SommaRighe", "CodiceFiscale", "Fornitore", "TipoDoc", "DataDoc", "NumeroDoc", "ImportoTotaleDocumento", "Causale", "CodiceCIG", "NumeroLinea", "Descrizione", "RigaFT-NC", "StatoVerifica", "MeseCompetenza", "AnnoCompetenza", "Servizio", "Beneficiario", "Presente_Beneficiario", "Pres_Ben_Fattura"}),
RimuoviColonnaBen = Table.RemoveColumns(OrdinaColonne,{"Presente_Beneficiario"}),
// Filter only invoices with a beneficiary; this will be a table to be appended later.
// Now we group the remaining rows.
FattureConBeneficiario = Table.SelectRows(RimuoviColonnaBen, each ([Pres_Ben_Fattura] = 1)),
// Reference to two previous steps (before the last filter)
NuovoFiltro = RimuoviColonnaBen,
// Filter only invoices where the beneficiary is not present
FattureSenzaBeneficiaio = Table.SelectRows(NuovoFiltro, each ([Pres_Ben_Fattura] = 0)),
// The idea is to consolidate multiple invoice rows into a single row,
// concatenating the 'Descrizione' field and removing unnecessary fields
// (which are not included in the expansion).
