పవర్ క్వెరీలో డేటా పాత్‌లను పారామీటర్ చేయడం

మీరు ఇప్పటికే Microsoft Excelలో ఉచిత పవర్ క్వెరీ యాడ్-ఇన్ సాధనాలను ఉపయోగించడం ప్రారంభించినట్లయితే, అతి త్వరలో మీరు సోర్స్ డేటాకు లింక్‌లను నిరంతరం విచ్ఛిన్నం చేయడంతో అత్యంత ప్రత్యేకమైన, కానీ చాలా తరచుగా మరియు బాధించే సమస్యను ఎదుర్కొంటారు. సమస్య యొక్క సారాంశం ఏమిటంటే, మీ ప్రశ్నలో మీరు బాహ్య ఫైల్‌లు లేదా ఫోల్డర్‌లను సూచిస్తే, ప్రశ్న టెక్స్ట్‌లో పవర్ క్వెరీ వాటికి సంపూర్ణ మార్గాన్ని హార్డ్‌కోడ్ చేస్తుంది. మీ కంప్యూటర్‌లో ప్రతిదీ బాగానే ఉంది, కానీ మీరు మీ సహోద్యోగులకు అభ్యర్థనతో ఫైల్‌ను పంపాలని నిర్ణయించుకుంటే, వారు నిరాశ చెందుతారు, ఎందుకంటే. వారు తమ కంప్యూటర్‌లోని సోర్స్ డేటాకు వేరే మార్గాన్ని కలిగి ఉన్నారు మరియు మా ప్రశ్న పని చేయదు.

అటువంటి పరిస్థితిలో ఏమి చేయాలి? కింది ఉదాహరణతో ఈ కేసును మరింత వివరంగా పరిశీలిద్దాం.

సమస్య యొక్క సూత్రీకరణ

మనం ఫోల్డర్‌లో ఉన్నామని అనుకుందాం E:అమ్మకాల నివేదికలు ఫైల్ ఉంది టాప్ 100 ఉత్పత్తులు.xls, ఇది మా కార్పొరేట్ డేటాబేస్ లేదా ERP సిస్టమ్ (1C, SAP, మొదలైనవి) నుండి అప్‌లోడ్ చేయబడినది.

ఈ రూపంలో ఎక్సెల్‌లో దానితో పని చేయడం దాదాపు అసాధ్యమని బ్యాట్‌లో స్పష్టంగా అర్థం చేసుకోవచ్చు: డేటాతో ఒకదాని ద్వారా ఖాళీ వరుసలు, విలీనమైన సెల్‌లు, అదనపు నిలువు వరుసలు, బహుళ-స్థాయి హెడర్ మొదలైనవి జోక్యం చేసుకుంటాయి.

అందువల్ల, అదే ఫోల్డర్‌లోని ఈ ఫైల్ పక్కన, మేము మరొక కొత్త ఫైల్‌ను సృష్టిస్తాము Handler.xlsx, దీనిలో మేము పవర్ క్వెరీ క్వెరీని సృష్టిస్తాము, అది సోర్స్ అప్‌లోడ్ ఫైల్ నుండి అగ్లీ డేటాను లోడ్ చేస్తుంది టాప్ 100 ఉత్పత్తులు.xls, మరియు వాటిని క్రమంలో ఉంచండి:

బాహ్య ఫైల్‌కు అభ్యర్థన చేయడం

ఫైల్‌ను తెరవడం Handler.xlsx, ట్యాబ్‌లో ఎంచుకోండి సమాచారం కమాండ్ డేటాను పొందండి – ఫైల్ నుండి – Excel వర్క్‌బుక్ నుండి (డేటా — డేటా పొందండి — ఫైల్ నుండి — Excel నుండి), అప్పుడు మూలం ఫైల్ యొక్క స్థానాన్ని మరియు మనకు అవసరమైన షీట్ను పేర్కొనండి. ఎంచుకున్న డేటా పవర్ క్వెరీ ఎడిటర్‌లో లోడ్ చేయబడుతుంది:

వాటిని సాధారణ స్థితికి తీసుకువద్దాం:

1. దీనితో ఖాళీ లైన్‌లను తొలగించండి హోమ్ - పంక్తులను తొలగించండి - ఖాళీ పంక్తులను తొలగించండి (హోమ్ — అడ్డు వరుసలను తీసివేయి — ఖాళీ అడ్డు వరుసలను తీసివేయి).
2. అనవసరమైన టాప్ 4 లైన్లను తొలగించండి హోమ్ - అడ్డు వరుసలను తొలగించండి - ఎగువ వరుసలను తొలగించండి (హోమ్ — అడ్డు వరుసలను తీసివేయి — పై వరుసలను తీసివేయి).
3. బటన్‌తో మొదటి అడ్డు వరుసను టేబుల్ హెడర్‌కు పెంచండి మొదటి పంక్తిని హెడర్‌లుగా ఉపయోగించండి టాబ్ హోమ్ (హోమ్ — మొదటి వరుసను హెడర్‌గా ఉపయోగించండి).
4. ఆదేశాన్ని ఉపయోగించి రెండవ నిలువు వరుసలోని ఉత్పత్తి పేరు నుండి ఐదు అంకెల కథనాన్ని వేరు చేయండి స్ప్లిట్ కాలమ్ టాబ్ ట్రాన్స్ఫర్మేషన్ (రూపాంతరం — స్ప్లిట్ కాలమ్).
5. మెరుగైన దృశ్యమానత కోసం అనవసరమైన నిలువు వరుసలను తొలగించి, మిగిలిన వాటి శీర్షికల పేరు మార్చండి.

ఫలితంగా, మేము ఈ క్రింది, మరింత ఆహ్లాదకరమైన చిత్రాన్ని పొందాలి:

ఈ ఎనోబుల్డ్ టేబుల్‌ని మా ఫైల్‌లోని షీట్‌కి తిరిగి అప్‌లోడ్ చేయడానికి ఇది మిగిలి ఉంది Handler.xlsx జట్టు మూసివేయండి మరియు డౌన్‌లోడ్ చేయండి (హోమ్ — మూసి&లోడ్ చేయండి) టాబ్ హోమ్:

అభ్యర్థనలో ఫైల్‌కి మార్గాన్ని కనుగొనడం

ఇప్పుడు మన ప్రశ్న “అండర్ ది హుడ్” ఎలా ఉందో చూద్దాం, అంతర్గత భాషలో “M” అనే సంక్షిప్త పేరుతో పవర్ క్వెరీలో నిర్మించబడింది. దీన్ని చేయడానికి, కుడి పేన్‌లో దానిపై డబుల్ క్లిక్ చేయడం ద్వారా మా ప్రశ్నకు తిరిగి వెళ్లండి అభ్యర్థనలు మరియు కనెక్షన్లు మరియు ట్యాబ్‌లో సమీక్ష ఎంచుకోండి అధునాతన ఎడిటర్ (వీక్షణ — అధునాతన ఎడిటర్):

తెరుచుకునే విండోలో, రెండవ పంక్తి వెంటనే మా అసలు అప్‌లోడ్ ఫైల్‌కి హార్డ్-కోడెడ్ మార్గాన్ని వెల్లడిస్తుంది. మేము ఈ టెక్స్ట్ స్ట్రింగ్‌ను పారామీటర్, వేరియబుల్ లేదా ఈ పాత్ ముందే వ్రాసిన ఎక్సెల్ షీట్ సెల్‌కి లింక్‌తో భర్తీ చేయగలిగితే, మేము దానిని తర్వాత సులభంగా మార్చవచ్చు.

ఫైల్ పాత్‌తో స్మార్ట్ టేబుల్‌ని జోడించండి

ప్రస్తుతానికి పవర్ క్వెరీని మూసివేసి, మన ఫైల్‌కి తిరిగి వెళ్దాం Handler.xlsx. కొత్త ఖాళీ షీట్‌ను జోడించి, దానిపై చిన్న “స్మార్ట్” పట్టికను తయారు చేద్దాం, దానిలోని ఏకైక సెల్‌లో మన సోర్స్ డేటా ఫైల్‌కి పూర్తి మార్గం వ్రాయబడుతుంది:

సాధారణ పరిధి నుండి స్మార్ట్ పట్టికను సృష్టించడానికి, మీరు కీబోర్డ్ సత్వరమార్గాన్ని ఉపయోగించవచ్చు Ctrl+T లేదా బటన్ పట్టికగా ఫార్మాట్ చేయండి టాబ్ హోమ్ (హోమ్ — టేబుల్ లాగా ఫార్మాట్ చేయండి). నిలువు వరుస శీర్షిక (సెల్ A1) ఖచ్చితంగా ఏదైనా కావచ్చు. క్లారిటీ కోసం నేను టేబుల్‌కి పేరు పెట్టాను అని కూడా గమనించండి పారామీటర్లు టాబ్ నమూనా రచయిత (రూపకల్పన).

ఎక్స్‌ప్లోరర్ నుండి మార్గాన్ని కాపీ చేయడం లేదా దానిని మాన్యువల్‌గా నమోదు చేయడం కూడా చాలా కష్టం కాదు, అయితే మానవ కారకాన్ని తగ్గించడం మరియు సాధ్యమైతే స్వయంచాలకంగా మార్గాన్ని నిర్ణయించడం ఉత్తమం. ఇది ప్రామాణిక Excel వర్క్‌షీట్ ఫంక్షన్‌ని ఉపయోగించి అమలు చేయవచ్చు సెల్ (సెల్), ఇది ఆర్గ్యుమెంట్‌గా పేర్కొన్న సెల్ గురించి ఉపయోగకరమైన సమాచారాన్ని అందించగలదు – ప్రస్తుత ఫైల్‌కి మార్గంతో సహా:

సోర్స్ డేటా ఫైల్ ఎల్లప్పుడూ మన ప్రాసెసర్ ఉన్న అదే ఫోల్డర్‌లో ఉంటుందని మేము అనుకుంటే, మనకు అవసరమైన మార్గాన్ని క్రింది ఫార్ములా ద్వారా రూపొందించవచ్చు:

=ఎడమ(సెల్("ఫైల్ పేరు");కనుగొను("[";సెల్("ఫైల్ పేరు"))-1)&"టాప్ 100 ఉత్పత్తులు.xls"

లేదా ఆంగ్ల సంస్కరణలో:

=ఎడమ(సెల్ ("ఫైల్ పేరు");కనుగొను("[«;సెల్("ఫైల్ పేరు"))-1)&»Топ-100 товаров.xls»

… ఫంక్షన్ ఎక్కడ ఉంది LEVSIMV (ఎడమ) పూర్తి లింక్ నుండి ప్రారంభ స్క్వేర్ బ్రాకెట్ (అంటే ప్రస్తుత ఫోల్డర్‌కి మార్గం) వరకు టెక్స్ట్ యొక్క భాగాన్ని తీసుకుంటుంది, ఆపై మా సోర్స్ డేటా ఫైల్ పేరు మరియు పొడిగింపు దానికి అతికించబడుతుంది.

ప్రశ్నలోని మార్గాన్ని పారామీటర్ చేయండి

చివరి మరియు అత్యంత ముఖ్యమైన టచ్ మిగిలి ఉంది - అభ్యర్థనలో సోర్స్ ఫైల్‌కు మార్గాన్ని వ్రాయడానికి టాప్ 100 ఉత్పత్తులు.xls, మేము సృష్టించిన “స్మార్ట్” పట్టికలోని సెల్ A2ని సూచిస్తోంది పారామీటర్లు.

దీన్ని చేయడానికి, పవర్ క్వెరీ ప్రశ్నకు తిరిగి వెళ్లి దాన్ని మళ్లీ తెరవండి అధునాతన ఎడిటర్ టాబ్ సమీక్ష (వీక్షణ — అధునాతన ఎడిటర్). కోట్‌లలో టెక్స్ట్ స్ట్రింగ్-పాత్‌కు బదులుగా “E:Sales reportsTop 100 products.xlsx” కింది నిర్మాణాన్ని పరిచయం చేద్దాం:

Excel.CurrentWorkbook(){[పేరు=”సెట్టింగ్‌లు”]}[కంటెంట్]0 {}[మూల డేటాకు మార్గం]

ఇది ఏమి కలిగి ఉందో చూద్దాం:

• Excel.CurrentWorkbook() ప్రస్తుత ఫైల్ యొక్క కంటెంట్‌లను యాక్సెస్ చేయడానికి M భాష యొక్క విధి
• {[పేరు=”సెట్టింగ్‌లు”]}[కంటెంట్] - ఇది మునుపటి ఫంక్షన్‌కు శుద్ధీకరణ పరామితి, మేము "స్మార్ట్" పట్టికలోని విషయాలను పొందాలనుకుంటున్నామని సూచిస్తుంది పారామీటర్లు
• [మూల డేటాకు మార్గం] అనేది పట్టికలోని నిలువు వరుస పేరు పారామీటర్లుదానికి మేము సూచిస్తాము
  
• 0 {} అనేది పట్టికలోని వరుస సంఖ్య పారామీటర్లుదాని నుండి మనం డేటా తీసుకోవాలనుకుంటున్నాము. టోపీ లెక్కించబడదు మరియు నంబరింగ్ ఒకటి నుండి కాకుండా సున్నా నుండి ప్రారంభమవుతుంది.

నిజానికి అంతే.

ఇది క్లిక్ చేయడానికి మిగిలి ఉంది ముగించు మరియు మా అభ్యర్థన ఎలా పనిచేస్తుందో తనిఖీ చేయండి. ఇప్పుడు, రెండు ఫైల్‌లతో ఉన్న మొత్తం ఫోల్డర్‌ను మరొక PCకి పంపుతున్నప్పుడు, అభ్యర్థన పనిచేస్తూనే ఉంటుంది మరియు డేటాకు మార్గాన్ని స్వయంచాలకంగా నిర్ణయిస్తుంది.

• పవర్ క్వెరీ అంటే ఏమిటి మరియు మైక్రోసాఫ్ట్ ఎక్సెల్‌లో పని చేస్తున్నప్పుడు ఇది ఎందుకు అవసరం
• పవర్ క్వెరీకి ఫ్లోటింగ్ టెక్స్ట్ స్నిప్పెట్‌ను ఎలా దిగుమతి చేయాలి
• XNUMXD క్రాస్‌టాబ్‌ని పవర్ క్వెరీతో ఫ్లాట్ టేబుల్‌కి రీడిజైనింగ్ చేయడం