ayuda necesaria para combinar mis consultas

Un usuario Pregunto ✅

srkase

Hola amigos,

¿Alguien puede ayudar a combinar mis consultas que estoy usando en mi archivo pbix? Aqui esta mi consulta

let
    Source = Table.Combine({PROD1516, PROD1617, PROD1718, PROD1819, PROD1920, CAB1516, CAB1617, CAB1718, CAB1819, CAB1920, SPS1516, SPS1617, SPS1718, SPS1819, SPS1920}),
    #"Added Conditional Column" = Table.AddColumn(Source, "DOMESTIC", each if Text.StartsWith([PRODUCT], "DMS") then "DOMESTIC" 
	else if Text.StartsWith([PRODUCT], "SHR") then "DOMESTIC" 
	else if Text.StartsWith([PRODUCT], "FRH") then "DOMESTIC" 
	else if Text.StartsWith([PRODUCT], "CHR") then "DOMESTIC" 
	else if Text.StartsWith([PRODUCT], "RAJAGOLD") then "DOMESTIC" 
	else if Text.StartsWith([PRODUCT], "RAJAMINI") then "DOMESTIC" 
	else if Text.StartsWith([PRODUCT], "ASMSP") then "DOMESTIC" 
		else if Text.StartsWith([PRODUCT], "HTT") then "DOMESTIC" else "OTHERS"),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "DISTZERO", each if [TOTALVAL] = 0 then "DISTRICT ZERO" else "DT"),
    #"CABLES CUSTOM COLUMN" = Table.AddColumn(#"Added Conditional Column1", "Custom", 
	each if [PRODUCT] = "SUBMERSIBLE FLAT CABLE 3 CORE 1*3*1.5 SQ.MM" then "1.5 BG" 
	else if [PRODUCT] = "SUBMERSIBLE FLAT CABLE 3 CORE 1*3*2.5 SQ.MM" then "2.5 BG" 
	else if [PRODUCT] = "SUBMERSIBLE FLAT CABLE 3 CORE 1*3*4.0 SQ.MM" then "4.0 BG" 
	else if [PRODUCT] = "SUBMERSIBLE FLAT CABLE 3 CORE 1*3*6.0 SQ.MM" then "6.0 BG" 
	 
	else if [PRODUCT] = """rajan"" 1X3X6.0 SQ.MM XLPE FLAT CABLE (LW-XLPE CL)" then "6.0 XLPECL" else "EMP"),
    #"Renamed Columns" = Table.RenameColumns(#"CABLES CUSTOM COLUMN",{{"Custom", "CABLE SIZE"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","[CANCELLED] ","",Replacer.ReplaceText,{"DEALER"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","[CANCELLED] ","",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","[CANCELED]","",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","[CANCELLED ] ","",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","[CENCELLED] ","",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","[cancelled]","",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","TEX MARKETING AGENCIES.","TEX MARKETING AGENCIES",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","PREMIER MARKETING","PREMIER ENGINEERS",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","[CANCELLED]","",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"BANGALORE TO BENGALURU" = Table.ReplaceValue(#"Replaced Value8","BANGALORE","BENGALURU",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"HOSKOTE TO HOSOKOTE" = Table.ReplaceValue(#"BANGALORE TO BENGALURU","HOSKOTE","HOSOKOTE",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"PREMIER MKT TO ENGINEERS" = Table.ReplaceValue(#"HOSKOTE TO HOSOKOTE","PREMIER MARKETING","PREMIER ENGINEERS",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"THAMMAM PATTY TO THAMMAMPATTY" = Table.ReplaceValue(#"PREMIER MKT TO ENGINEERS","THAMMAM PATTY","THAMMAMPATTY",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"ADP TO A.D.P.CO" = Table.ReplaceValue(#"THAMMAM PATTY TO THAMMAMPATTY","A.D.P ELECTRICALS CHINGLEPET","A.D.P.CO., CHENGALPATTU",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"MANI ELECTRICALS" = Table.ReplaceValue(#"ADP TO A.D.P.CO","MANI ELECTRICALS [VILU]","MANI ELECTRICALS",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"PADMANABHAN ELECTRICALS" = Table.ReplaceValue(#"MANI ELECTRICALS","PADMANABHA ELECTRICALS","PADMANABAN ELECTRICALS",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"SRI SELVAVAGANAPTHY" = Table.ReplaceValue(#"PADMANABHAN ELECTRICALS","SRI SELVAGANAPATHY ENGINEERING PANJATTI","SRI SELVA GANAPATHY ENGINEERING PONNERI",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"SUSEELA ELECTRICALS" = Table.ReplaceValue(#"SRI SELVAVAGANAPTHY","SUSEELA ELECTRICALS PANRUTTI","SUSILA ELECTRICALS PANRUTI",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"ANANDA PIPES" = Table.ReplaceValue(#"SUSEELA ELECTRICALS","ANANDA PIPES","ANANTHA PIPES",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"BOMBAY ELECTRICALS" = Table.ReplaceValue(#"ANANDA PIPES","BOMBAY ELECTRICALS","BOMBAY MOTOR & PIPES",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"FATIMA ELECTRICALS" = Table.ReplaceValue(#"BOMBAY ELECTRICALS","FATIMA ELECTRICALS","FATIMA AGENCY",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"KANDAN ELECTRICALS" = Table.ReplaceValue(#"FATIMA ELECTRICALS","KANDAN ELECTRICALS","KANDAN ELECTRICAL AGENCIES",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"KASIM STORES" = Table.ReplaceValue(#"KANDAN ELECTRICALS","KASIM STORES SIVAGANGAI DIST","KASIM STORES KARAIKUDI",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"SIDDHIAH KVL TO VILATHI" = Table.ReplaceValue(#"KASIM STORES","SIDDHIAH ELECTRICALS KOVILPATTI","SIDDHIAH ELECTRICALS VILLATHIKULAM",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"SIDDHIAH VIL TO VILLATHI" = Table.ReplaceValue(#"SIDDHIAH KVL TO VILATHI","SIDDHIAH ELECTRICALS VILATHIKULAM","SIDDHIAH ELECTRICALS VILLATHIKULAM",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"YUGAN PUMPS" = Table.ReplaceValue(#"SIDDHIAH VIL TO VILLATHI","THIRUMANGALAM,","THIRUMANGALAM",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"MDU PIPES" = Table.ReplaceValue(#"YUGAN PUMPS","MADURAI PIPE MADURAI TOWN","MADURAI PIPES MADURAI TOWN",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"DEEPIKA ELECTRICALS" = Table.ReplaceValue(#"MDU PIPES","DEEPIKA ELECTRICALS [MULANUR] MULANUR","DEEPIKA ELECTRICALS MULANUR",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"BASAVESWARA AGENCIES" = Table.ReplaceValue(#"DEEPIKA ELECTRICALS","BASAVESWARA AGENCIES [DAVANAGERE] DAVANAGERE","BASAVESHWARA AGENCIES [DAVANAGERE] DAVANAGERE",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"MSR ENGINEERING" = Table.ReplaceValue(#"BASAVESWARA AGENCIES","MSR ENGINEERING NAYUDUPET","MSR ENGINEERINGS NAIDUPET",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"PANCHSHEEL TRADERS" = Table.ReplaceValue(#"MSR ENGINEERING","PANCHSHEEL TRADERS [HUB] GHATAPRABHA","PANCHASHEEL TRADERS [HUB] GHATAPRABHA",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    SEMITRONICS = Table.ReplaceValue(#"PANCHSHEEL TRADERS","SEMITRONICS THODUPUZHA","SEMITRONICS [THO] THODUPUZHA",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"RAI AGARWAL MACHINERY" = Table.ReplaceValue(SEMITRONICS,"AGARWAL MACHINERY JANJGIR","AGARWAL MACHINERY STORES JANJGIR-NAILA",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"RAI AMAR BROS" = Table.ReplaceValue(#"RAI AGARWAL MACHINERY","AMAR BRO'S RAJNANDGAON","AMAR BROTHERS RAJNANDGAON",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"RAI BHATIA" = Table.ReplaceValue(#"RAI AMAR BROS","BHATIA ELECTRICAL & GENERAL STORES DONGARGARH","BHATIA ELECTRICALS & GENERAL STORE DONGARGARH",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"RAI GUPTA TRDS" = Table.ReplaceValue(#"RAI BHATIA","GUPTA TRADERS BEKUNTHPUR","GUPTA TRADERS BAIKUNTHPUR",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"RAI GURUNANAK" = Table.ReplaceValue(#"RAI GUPTA TRDS","GURU NANAK HARDWARE STORES KANKER","GURUNANAK HARDWARE STORES KANKER",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"RAI SHIVSHANKAR" = Table.ReplaceValue(#"RAI GURUNANAK","SHIVSHANKAR ELECTRICALS KAWARDHA","SHIVSHANKAR ELECTRICALS [RAI] KAWARDHA",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"RAI MUKESH ELECT" = Table.ReplaceValue(#"RAI SHIVSHANKAR","MUKESH ELECTRICAL & ELECTRONIC DHARAMJAYGARH","MUKESH ELECTRICAL & ELECTRONIC DHARAMJAIGARH",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"RAI NANAGRAM MURLI" = Table.ReplaceValue(#"RAI MUKESH ELECT","NANAGRAM MURLIDHAR BILASAPUR","NANAGRAM MURLIDHAR BILASPUR",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"RAI CHATTISGARH" = Table.ReplaceValue(#"RAI NANAGRAM MURLI","CHHATISGARH KRISHISEVA KENDRA AMBIKAPUR","CHHATTISGARH PUMP & MACHINERY AMBIKAPUR",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"IND A.K.ELECTRICALS" = Table.ReplaceValue(#"RAI CHATTISGARH","A.K.ELECTRICAL VAIDHAN","A.K.ELECTRICAL WAIDHAN",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"IND BHARAT ELEC MHOW" = Table.ReplaceValue(#"IND A.K.ELECTRICALS","BHARAT ELCLS. & MACHNY. STORES MHOW","BHARAT ELCLS. & MACHNY. STORES [MHOW] MHOW",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"IND GOPALDAS HIRA" = Table.ReplaceValue(#"IND BHARAT ELEC MHOW","GOPALDAS HIRANAND & SONS REWA","GOPALDAS HIRANAND PRIVATE LIMITED GOLPARK",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"IND HARIOM KRISHI" = Table.ReplaceValue(#"IND GOPALDAS HIRA","HARI OM KRISHI SEWA KENDRA GAUTAMPURA/SANWER","HARI OM KRISHI SEWA KENDRA GAUTAMPURA",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"IND JAIN ELECTRICALS" = Table.ReplaceValue(#"IND HARIOM KRISHI","JAIN ELECTRICALS BINAGUNJ","JAIN ELECTRICALS BINAGANJ",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"IND KANCHAN MACHINERY" = Table.ReplaceValue(#"IND JAIN ELECTRICALS","KANCHAN MACHINERY SHYAMGARH","KANCHAN MACHINERY SHAMGARH",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"IND SNT ENTERPRISES" = Table.ReplaceValue(#"IND KANCHAN MACHINERY","S.N.T. ENTER PRISES TAL","S N T ENTERPRISES TAL",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"IND MAHESWARI PIPE" = Table.ReplaceValue(#"IND SNT ENTERPRISES","MAHESWARI PIPE FITTINGS CO. BHOPAL","MAHESHWARI PIPE FITTING CO. BHOPAL",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"JAI GEHLOT" = Table.ReplaceValue(#"IND MAHESWARI PIPE","GEHLOT MACHINERY STORE [DMN] DHORIMANA","GEHLOT MACHINERY STORE [DMN] DHORIMANNA",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"JAI GEHLOT DMN1" = Table.ReplaceValue(#"JAI GEHLOT","GEHLOT MACHINERY STORES [DHO] DHORIMANA","GEHLOT MACHINERY STORE [DMN] DHORIMANNA",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"JAI GAURISHANKAR" = Table.ReplaceValue(#"JAI GEHLOT DMN1","GOURI SHANKAR FILLING STATION MAULLASAR","GOURISHANKAR FILLING STORE MULLASAR",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"JAI GAURISHANKAR1" = Table.ReplaceValue(#"JAI GAURISHANKAR","GOURI SHANKAR FILLING STORE DIDWANA","GOURISHANKAR FILLING STORE MULLASAR",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"JAI HIND IRRIGATION" = Table.ReplaceValue(#"JAI GAURISHANKAR1","HIND IRRIGATION PRIVATE LTD GANGAPUR","HIND IRRIGATION GANGAPUR",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"JAI JAIRAMPIPE" = Table.ReplaceValue(#"JAI HIND IRRIGATION","JAI RAM PIPE TRADERS DUNGARGARH","JAI RAM PIPE TRADERS SRI DUNGARGARH",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"JAI MAHESWARIAGY" = Table.ReplaceValue(#"JAI JAIRAMPIPE","MAHESHWARI AGENCIES [BEWAR] BEAWAR","MAHESHWARI AGENCIES [BEWAR] BEAWER",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"JAI PRAKASHAGY" = Table.ReplaceValue(#"JAI MAHESWARIAGY","SHREE PRAKASH AGENCY PIPLU","SHREE PRAKASH AGENCY [PIPLU] PIPLU",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"JAI SAMARTH" = Table.ReplaceValue(#"JAI PRAKASHAGY","SHRI SAMARTH ELECTRIC STORE BIJAINAGAR","SHRI SAMARATH ELECTRIC STORE BIJAI NAGAR,",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"JAI SAMARTH1" = Table.ReplaceValue(#"JAI SAMARTH","SAMARTH ELECTRIC STORE BIJAINAGAR","SHRI SAMARATH ELECTRIC STORE BIJAI NAGAR,",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"JAI SONYTRDS" = Table.ReplaceValue(#"JAI SAMARTH1","SONY TRADERS [OLD] JODHPUR","SONY TRADERS JODHPUR,",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"JAI SHIVSAKTHI" = Table.ReplaceValue(#"JAI SONYTRDS","SHIVSHAKTI ELECTRICALS SAYALA","SHIV SHAKTI ELECTRICALS (JAI) SAYALA,",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"JAI SURANA" = Table.ReplaceValue(#"JAI SHIVSAKTHI","SURANA ELECTRIC CHITTORGARH ","SURANA MACHINERY STORE CHITTORGARH,",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"SEC ARAMAKRISHNAMA"=Table.ReplaceValue(#"JAI SURANA","A.RAMAKRISHNAMMA SALES CORPORATION [KHM]. KHAMMAM","A.RAMAKRISHNAMMA SALES CORPORATION KHAMMAM",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"SEC KRISHNA"=Table.ReplaceValue(#"SEC ARAMAKRISHNAMA","KRISHNA AGENCIES KALWAKURTHY","KRISHNA AGENCIES. KALWAKURTHY",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"SEC MADEENA"=Table.ReplaceValue(#"SEC KRISHNA","MADEENA ENGINEERING & HARDWARE CHOPPADANDI","MADEENA ENGINEERING & ELECTRICALS CHOPPADANDI",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"SEC MAHADEV"=Table.ReplaceValue(#"SEC MADEENA","MAHADEV ELECTRICAL & ENGG. WORKS SRINIVAS NAGAR","MAHADEV ELECTRICAL & ENGG. WORKS SRINIVAS NAGAR,SIDDIPET",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"SEC RAGHU"=Table.ReplaceValue(#"SEC MAHADEV","RAGHU ELECTL, ELECTRONICS & GEN.STORES WYRA","RAGHU ELECTL,ELECTRONICS&GEN.STORES WYRA",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"SEC SAI VENKAT"=Table.ReplaceValue(#"SEC RAGHU","SAI VENKAT ELECTRICAL WORKS KOTHAKOTA","SAI VENKAT ELECTRICAL WORKS. KOTHAKOTA",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"SEC SATHYANARAYAN"=Table.ReplaceValue(#"SEC SAI VENKAT","SATYANARAYANA AND COMPANY [JAM] JAMMIKUNTA","SATYANARAYANA AND COMPANY JAMMIKUNTA",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"SEC SHIVA SAI"=Table.ReplaceValue(#"SEC SATHYANARAYAN","SHIVA SAI ELECTLS & ENGINEERING CO; CHANDUR","SHIVA SAI ELECTRICALS & ENG.,CO CHANDUR",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    #"SEC SRI LAXMI"=Table.ReplaceValue(#"SEC SHIVA SAI","SRI LAXMI ELECTRICALS & ENG.CO. MALL","SRI LAXMI ELECTRICALS & ENGG.CO MALL",Replacer.ReplaceText,{"DLR NAME_DEST"}),
    
in
    SEC SRI LAXMI

En respuesta a srkase

Hola @srkase,

Antes de combinar estas consultas, he creado un archivo de muestra que le ayudará a comprender:

muestra multireplace

En esta muestra, he creado una tabla que incluye Oldtext y Newtext, luego usé estos códigos en el editor avanzado:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WinZ29HN29fFxdYlVMFSK1YGLgASMkAWAIgqxCsZQIVe4LhOISHJiXnJqTk5qSqwpWCDENULB1zHI2zXE089dwdEdqMHTNVjPDCwZEOTq6+kahFBgjmZTrAVYwMnRz93Rxz/I1RLM9fAP9vYPcTU0wG6IIdADsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"DLR NAME_DEST" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DLR NAME_DEST", type text}}),
    ReplaceTable = Table.Buffer(Replacer),
    ReplaceStep = Table.ReplaceValue(#"Changed Type",each [DLR NAME_DEST], each 
let 
v=[DLR NAME_DEST],
t = Table.SelectRows(ReplaceTable, each Text.Contains(v,[OldText]))
in if Table.RowCount
,Replacer.ReplaceText,{"DLR NAME_DEST"})


in
    ReplaceStep

Los detalles sobre el resultado reemplazado puede verlos usted mismo en el archivo de muestra.

Ahora podemos consultar este ejemplo para combinar estas consultas, recuerde hacer una copia de seguridad de sus consultas anteriores para evitar:

let
    Source = Table.Combine({PROD1516, PROD1617, PROD1718, PROD1819, PROD1920, CAB1516, CAB1617, CAB1718, CAB1819, CAB1920, SPS1516, SPS1617, SPS1718, SPS1819, SPS1920}),
    #"Added Conditional Column" = Table.AddColumn(Source, "DOMESTIC", 
    each if Text.StartsWith([PRODUCT], "DMS") then "DOMESTIC" 
        else if Text.StartsWith([PRODUCT], "SHR") then "DOMESTIC" 
        else if Text.StartsWith([PRODUCT], "FRH") then "DOMESTIC" 
        else if Text.StartsWith([PRODUCT], "CHR") then "DOMESTIC" 
        else if Text.StartsWith([PRODUCT], "RAJAGOLD") then "DOMESTIC" 
        else if Text.StartsWith([PRODUCT], "RAJAMINI") then "DOMESTIC" 
        else if Text.StartsWith([PRODUCT], "ASMSP") then "DOMESTIC" 
        else if Text.StartsWith([PRODUCT], "HTT") then "DOMESTIC" 
        else "OTHERS"),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "DISTZERO", each if [TOTALVAL] = 0 then "DISTRICT ZERO" else "DT"),
    #"CABLES CUSTOM COLUMN" = Table.AddColumn(#"Added Conditional Column1", "CABLE SIZE", 
    each if [PRODUCT] = "SUBMERSIBLE FLAT CABLE 3 CORE 1*3*1.5 SQ.MM" then "1.5 BG" 
        else if [PRODUCT] = "SUBMERSIBLE FLAT CABLE 3 CORE 1*3*2.5 SQ.MM" then "2.5 BG" 
        else if [PRODUCT] = "SUBMERSIBLE FLAT CABLE 3 CORE 1*3*4.0 SQ.MM" then "4.0 BG" 
        else if [PRODUCT] = "SUBMERSIBLE FLAT CABLE 3 CORE 1*3*6.0 SQ.MM" then "6.0 BG" 
        else if [PRODUCT] = """rajan"" 1X3X6.0 SQ.MM XLPE FLAT CABLE (LW-XLPE CL)" then "6.0 XLPECL" 
        else "EMP"),
        
    #"Replaced Value" = Table.ReplaceValue(CABLES CUSTOM COLUMN,"[CANCELLED] ","",Replacer.ReplaceText,{"DEALER"}),
    
    ReplaceTable = Table.Buffer(Replacer),
    ReplaceStep = Table.ReplaceValue(#"Replaced Value",each [DLR NAME_DEST], each 
            let 
                v = [DLR NAME_DEST],
                t = Table.SelectRows(ReplaceTable, each Text.Contains(v,[OldText]))
            in 
                if Table.RowCount
            ,Replacer.ReplaceText,{"DLR NAME_DEST"})
    
in
    ReplaceStep

Además, no estoy seguro de si combinar estas consultas puede mejorar el rendimiento, puede costar mucho rendimiento de esta manera, por lo que depende de si lo adoptas.

Atentamente,
Yingjie Li

Si esta publicación ayuda, considere Aceptarla como la solución para ayudar a los demás miembros a encontrarla más rápidamente.

Hola @srkase,

¿Podría compartir algunas consultas como PROD1516, PROD1617 .. y proporcionar algunos datos como un archivo .pbix de muestra para una mayor discusión?

Consulte esta publicación sobre cómo obtener una respuesta rápida a su pregunta:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Atentamente,
Yingjie Li

srkase

En respuesta a v-yingjl

ADEMÁS PROD1516, PROD1617, PR0D1718, prod1819, prod1920 ETC., SON VENTAS ANUALES INDIVIDUALES que tienen todos los nombres de columna comúnmente … HE COMBINADO ESTOS ARCHIVOS DE EXCEL EN UN SOLO ARCHIVO Y LO NOMBRE COMO PROD_CABLE CON LOS MISMOS NOMBRES DE COLUMNA Y AÑADIR COLUMNAS CONDICIONALES COMO SE INDICA EN LA CONSULTA. Por qué estoy usando el reemplazo de textos es,

1. Actualizo mi hoja de Excel con registros semanales.

2. Los registros recién agregados tendrán dichos valores para ser reemplazados.

srkase

En respuesta a v-yingjl

mis datos de muestra

YEAR	SHORTNAME	DEALER	TYPE	MAJOR GROUP	MINOR GROUP	SUB GROUP	PRODUCT	VARIANT	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR	TOTAL QTY	TOTALVAL	PTA_DESTINATION	UNIT_CODE	PTA_DISTRICT	PTA_TALUK	PTA_PIN	ACP_ENTRYDT	HP	PER	APR VAL	MAY VAL	JUN VAL	JUL VAL	AUG VAL	SEP VAL	OCT VAL	NOV VAL	DEC VAL	JAN VAL	FEB VAL	MAR VAL	STG	STG TOT	TOTAL CR	DLR NAME_DEST	DEST_DST_PIN	DST_DEST_PIN	PIN_DEST_DST	CATEGORY	DLR YR
20302031	DEMO	DEMO1	STD       	SINGLE PHASE DOMESTIC MB	DMH	DMH	D1		5	0	5	0	0	0	0	0	0	0	0	0	10	36218	RAJANNAGAR	U1	SURENDRA NAGAR                                              	WADHWANICITY                                                	363001    	00-Jan-1900 12:00 AM	0.5	0	0	0	0	0	0	0	0	0	0	0	0	0	10	100	0.0000	DEMO1 RAJANNAGAR	RAJANNAGARSURENDRA NAGAR 363001	SURENDRA NAGAR RAJANNAGAR363001	363001 RAJANNAGARSURENDRA NAGAR	PRODUCT	1900
20192020	DEMO	DEMO2	STD       	SINGLE PHASE DOMESTIC MB	DMH	DMH	D2		5	0	5	0	0	0	0	0	0	0	0	0	10	36218	RAJANNAGAR	U2	SURENDRA NAGAR                                              	WADHWANICITY                                                	363001    	01-Apr-2015 6:31 PM	0.5		18109	0	18109	0	0	0	0	0	0	0	0	0	0	0	0.0036	DEMO2 RAJANNAGAR	RAJANNAGARSURENDRA NAGAR 363001	SURENDRA NAGAR RAJANNAGAR363001	363001 RAJANNAGARSURENDRA NAGAR	PRODUCT	2015
20192020	DEMO	DEMO3	STD       	SINGLE PHASE DOMESTIC MB	DMS	DMS	D3		3	0	2	0	0	0	0	0	0	0	0	0	5	26823	RAJANNAGAR	U1	SURENDRA NAGAR                                              	WADHWANICITY                                                	363001    	01-Apr-2015 6:31 PM	0.5		16094	0	10729	0	0	0	0	0	0	0	0	0	0	0	0.0027	DEMO3 RAJANNAGAR	RAJANNAGARSURENDRA NAGAR 363001	SURENDRA NAGAR RAJANNAGAR363001	363001 RAJANNAGARSURENDRA NAGAR	PRODUCT	2015
20192020	DEMO	DEMO4	STD       	SINGLE PHASE DOMESTIC MB	HCS	HCS4-SERIES	D45		0	0	1	0	0	0	0	0	0	0	0	0	1	4746	RAJANNAGAR	U1	SURENDRA NAGAR                                              	WADHWANICITY                                                	363001    	01-Apr-2015 6:31 PM	0.5		0	0	4746	0	0	0	0	0	0	0	0	0	0	0	0.0005	DEMO4 RAJANNAGAR	RAJANNAGARSURENDRA NAGAR 363001	SURENDRA NAGAR RAJANNAGAR363001	363001 RAJANNAGARSURENDRA NAGAR	PRODUCT	2015
20192020	DEMO	DEMO5	STD       	OUTSOURCE	ASM	ASM14-SERIES	D45		0	0	1	0	0	0	0	0	0	0	0	0	1	9340	RAJANNAGAR	U2	SURENDRA NAGAR                                              	WADHWANICITY                                                	363001    	01-Apr-2015 6:31 PM	2		0	0	9340	0	0	0	0	0	0	0	0	0	0	0	0.0009	DEMO5 RAJANNAGAR	RAJANNAGARSURENDRA NAGAR 363001	SURENDRA NAGAR RAJANNAGAR363001	363001 RAJANNAGARSURENDRA NAGAR	PRODUCT	2015
20192020	DEMO	DEMO6	STD       	OPENWELL MONOBLOCKS	THREE PHASE	ASM	D45	32BKC	0	1	0	0	0	0	0	0	0	0	0	0	1	20798	RAJANNAGAR	U3	SURENDRA NAGAR                                              	WADHWANICITY                                                	363001    	01-Apr-2015 6:31 PM	7.5		0	20798	0	0	0	0	0	0	0	0	0	0	0	0	0.0021	DEMO6 RAJANNAGAR	RAJANNAGARSURENDRA NAGAR 363001	SURENDRA NAGAR RAJANNAGAR363001	363001 RAJANNAGARSURENDRA NAGAR	PRODUCT	2015
20192020	DEMO	DEMO7	STD       	OPENWELL MONOBLOCKS	THREE PHASE	ASMJ	D45	32DKC	0	1	0	0	0	0	0	0	0	0	0	0	1	14981	RAJANNAGAR	U4	SURENDRA NAGAR                                              	WADHWANICITY                                                	363001    	01-Apr-2015 6:31 PM	7.5		0	14981	0	0	0	0	0	0	0	0	0	0	0	0	0.0015	DEMO7 RAJANNAGAR	RAJANNAGARSURENDRA NAGAR 363001	SURENDRA NAGAR RAJANNAGAR363001	363001 RAJANNAGARSURENDRA NAGAR	PRODUCT	2015
20192020	DEMO	DEMO8	STD       	OPENWELL MONOBLOCKS	THREE PHASE	ASMJ	D45	32CKC	0	0	2	0	0	0	0	0	0	0	0	0	2	21950	RAJANNAGAR	U5	SURENDRA NAGAR                                              	WADHWANICITY                                                	363001    	01-Apr-2015 6:31 PM	3		0	0	21950	0	0	0	0	0	0	0	0	0	0	0	0.0022	DEMO8 RAJANNAGAR	RAJANNAGARSURENDRA NAGAR 363001	SURENDRA NAGAR RAJANNAGAR363001	363001 RAJANNAGARSURENDRA NAGAR	PRODUCT	2015
20192020	DEMO	DEMO9	STD       	SURFACE MONOBLOCKS	THREE PHASE	AMH	D45	000	0	0	1	0	0	0	0	0	0	0	0	0	1	8820	RAJANNAGAR	U3	SURENDRA NAGAR                                              	WADHWANICITY                                                	363001    	01-Apr-2015 6:31 PM	1.5		0	0	8820	0	0	0	0	0	0	0	0	0	0	0	0.0009	DEMO9 RAJANNAGAR	RAJANNAGARSURENDRA NAGAR 363001	SURENDRA NAGAR RAJANNAGAR363001	363001 RAJANNAGARSURENDRA NAGAR	PRODUCT	2015
20192020	DEMO	DEMO10	STD       	SUBMERSIBLES	3" SET	JTS SET	D45	34CDA + 14FAA	0	0	0	1	0	0	0	0	0	0	0	0	1	8043	RAJANNAGAR	U5	SURENDRA NAGAR                                              	WADHWANICITY                                                	363001    	01-Apr-2015 6:31 PM	0.75		0	0	0	8043	0	0	0	0	0	0	0	0	14	14	0.0008	DEMO10 RAJANNAGAR	RAJANNAGARSURENDRA NAGAR 363001	SURENDRA NAGAR RAJANNAGAR363001	363001 RAJANNAGARSURENDRA NAGAR	PRODUCT	2015

En respuesta a srkase

Hola @srkase,

¿Podría organizar estos datos o compartirlos como un archivo? Ni siquiera puedo distinguir qué columnas son y cuáles son los valores correspondientes.

Atentamente,

Yingjie Li

srkase

En respuesta a v-yingjl

SEÑOR AQUÍ ESTÁN MIS DATOS EN EXCEL …

https: //www.dropbox.com/scl/fi/rucl1at8z55i5oh8rcasl/sample-data.xlsx? dl = 0 & rlkey = dyz357ol4qx8c0vgwzo …

En respuesta a srkase

Hola @srkase,

Antes de combinar estas consultas, he creado un archivo de muestra que le ayudará a comprender:

muestra multireplace

En esta muestra, he creado una tabla que incluye Oldtext y Newtext, luego usé estos códigos en el editor avanzado:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WinZ29HN29fFxdYlVMFSK1YGLgASMkAWAIgqxCsZQIVe4LhOISHJiXnJqTk5qSqwpWCDENULB1zHI2zXE089dwdEdqMHTNVjPDCwZEOTq6+kahFBgjmZTrAVYwMnRz93Rxz/I1RLM9fAP9vYPcTU0wG6IIdADsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"DLR NAME_DEST" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DLR NAME_DEST", type text}}),
    ReplaceTable = Table.Buffer(Replacer),
    ReplaceStep = Table.ReplaceValue(#"Changed Type",each [DLR NAME_DEST], each 
let 
v=[DLR NAME_DEST],
t = Table.SelectRows(ReplaceTable, each Text.Contains(v,[OldText]))
in if Table.RowCount
,Replacer.ReplaceText,{"DLR NAME_DEST"})


in
    ReplaceStep

Los detalles sobre el resultado reemplazado puede verlos usted mismo en el archivo de muestra.

Ahora podemos consultar este ejemplo para combinar estas consultas, recuerde hacer una copia de seguridad de sus consultas anteriores para evitar:

let
    Source = Table.Combine({PROD1516, PROD1617, PROD1718, PROD1819, PROD1920, CAB1516, CAB1617, CAB1718, CAB1819, CAB1920, SPS1516, SPS1617, SPS1718, SPS1819, SPS1920}),
    #"Added Conditional Column" = Table.AddColumn(Source, "DOMESTIC", 
    each if Text.StartsWith([PRODUCT], "DMS") then "DOMESTIC" 
        else if Text.StartsWith([PRODUCT], "SHR") then "DOMESTIC" 
        else if Text.StartsWith([PRODUCT], "FRH") then "DOMESTIC" 
        else if Text.StartsWith([PRODUCT], "CHR") then "DOMESTIC" 
        else if Text.StartsWith([PRODUCT], "RAJAGOLD") then "DOMESTIC" 
        else if Text.StartsWith([PRODUCT], "RAJAMINI") then "DOMESTIC" 
        else if Text.StartsWith([PRODUCT], "ASMSP") then "DOMESTIC" 
        else if Text.StartsWith([PRODUCT], "HTT") then "DOMESTIC" 
        else "OTHERS"),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "DISTZERO", each if [TOTALVAL] = 0 then "DISTRICT ZERO" else "DT"),
    #"CABLES CUSTOM COLUMN" = Table.AddColumn(#"Added Conditional Column1", "CABLE SIZE", 
    each if [PRODUCT] = "SUBMERSIBLE FLAT CABLE 3 CORE 1*3*1.5 SQ.MM" then "1.5 BG" 
        else if [PRODUCT] = "SUBMERSIBLE FLAT CABLE 3 CORE 1*3*2.5 SQ.MM" then "2.5 BG" 
        else if [PRODUCT] = "SUBMERSIBLE FLAT CABLE 3 CORE 1*3*4.0 SQ.MM" then "4.0 BG" 
        else if [PRODUCT] = "SUBMERSIBLE FLAT CABLE 3 CORE 1*3*6.0 SQ.MM" then "6.0 BG" 
        else if [PRODUCT] = """rajan"" 1X3X6.0 SQ.MM XLPE FLAT CABLE (LW-XLPE CL)" then "6.0 XLPECL" 
        else "EMP"),
        
    #"Replaced Value" = Table.ReplaceValue(CABLES CUSTOM COLUMN,"[CANCELLED] ","",Replacer.ReplaceText,{"DEALER"}),
    
    ReplaceTable = Table.Buffer(Replacer),
    ReplaceStep = Table.ReplaceValue(#"Replaced Value",each [DLR NAME_DEST], each 
            let 
                v = [DLR NAME_DEST],
                t = Table.SelectRows(ReplaceTable, each Text.Contains(v,[OldText]))
            in 
                if Table.RowCount
            ,Replacer.ReplaceText,{"DLR NAME_DEST"})
    
in
    ReplaceStep

Además, no estoy seguro de si combinar estas consultas puede mejorar el rendimiento, puede costar mucho rendimiento de esta manera, por lo que depende de si lo adoptas.

Atentamente,
Yingjie Li

Si esta publicación ayuda, considere Aceptarla como la solución para ayudar a los demás miembros a encontrarla más rápidamente.

srkase

En respuesta a v-yingjl

Funciona señor … pero el tiempo que tomó es más que mis consultas anteriores ..

Deja un comentario

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