పవర్ క్వెరీలో ఒక షీట్ నుండి మల్టీఫార్మాట్ టేబుల్‌లను రూపొందించడం

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

ఇన్‌పుట్ డేటాగా, మేము Excel ఫైల్‌ని కలిగి ఉన్నాము, ఇక్కడ షీట్‌లలో ఒకదానిలో క్రింది ఫారమ్ యొక్క విక్రయాల డేటాతో అనేక పట్టికలు ఉన్నాయి:

ఇది గమనించండి:

• విభిన్న పరిమాణాల పట్టికలు మరియు ఏ విధమైన క్రమబద్ధీకరణ లేకుండా అడ్డు వరుసలు మరియు నిలువు వరుసలలో వివిధ రకాల ఉత్పత్తులు మరియు ప్రాంతాలతో.
• పట్టికల మధ్య ఖాళీ పంక్తులను చొప్పించవచ్చు.
• పట్టికల సంఖ్య ఏదైనా కావచ్చు.
  

రెండు ముఖ్యమైన అంచనాలు. ఇది ఊహించబడింది:

• ప్రతి టేబుల్ పైన, మొదటి కాలమ్‌లో, టేబుల్ విక్రయాలను వివరించే మేనేజర్ పేరు ఉంది (ఇవనోవ్, పెట్రోవ్, సిడోరోవ్, మొదలైనవి)
  
• అన్ని పట్టికలలోని వస్తువులు మరియు ప్రాంతాల పేర్లు ఒకే విధంగా వ్రాయబడ్డాయి - కేస్ ఖచ్చితత్వంతో.
  

అంతిమ లక్ష్యం అన్ని పట్టికల నుండి డేటాను ఒక ఫ్లాట్ సాధారణీకరించిన పట్టికలో సేకరించడం, తదుపరి విశ్లేషణకు అనుకూలమైనది మరియు సారాంశాన్ని రూపొందించడం, అంటే ఇందులో:

దశ 1. ఫైల్‌కి కనెక్ట్ చేయండి

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

ఫలితంగా, దాని నుండి మొత్తం డేటా పవర్ క్వెరీ ఎడిటర్‌లో లోడ్ చేయబడాలి:

దశ 2. చెత్తను శుభ్రం చేయండి

స్వయంచాలకంగా రూపొందించబడిన దశలను తొలగించండి సవరించిన రకం (మార్చబడిన రకం) и ఎలివేటెడ్ హెడర్‌లు (ప్రమోట్ చేయబడిన శీర్షికలు) మరియు ఫిల్టర్‌ని ఉపయోగించి టోటల్‌లతో ఖాళీ లైన్‌లు మరియు లైన్‌లను వదిలించుకోండి శూన్య и మొత్తం మొదటి నిలువు వరుస ద్వారా. ఫలితంగా, మేము ఈ క్రింది చిత్రాన్ని పొందుతాము:

దశ 3. నిర్వాహకులను జోడించడం

ఎవరి అమ్మకాలు ఎక్కడ ఉన్నాయో తరువాత అర్థం చేసుకోవడానికి, మా పట్టికకు ఒక నిలువు వరుసను జోడించడం అవసరం, ఇక్కడ ప్రతి అడ్డు వరుసలో సంబంధిత ఇంటిపేరు ఉంటుంది. దీని కొరకు:

1. కమాండ్‌ని ఉపయోగించి లైన్ నంబర్‌లతో సహాయక కాలమ్‌ని జోడిద్దాం కాలమ్ జోడించండి – సూచిక కాలమ్ – 0 నుండి (కాలమ్ జోడించండి — సూచిక నిలువు వరుస — 0 నుండి).

2. కమాండ్‌తో ఫార్ములాతో నిలువు వరుసను జోడించండి కాలమ్‌ని కలుపుతోంది - అనుకూల కాలమ్ (నిలువు వరుసను జోడించండి - అనుకూల కాలమ్) మరియు అక్కడ కింది నిర్మాణాన్ని పరిచయం చేయండి:

ఈ ఫార్ములా యొక్క తర్కం చాలా సులభం - మొదటి నిలువు వరుసలోని తదుపరి సెల్ యొక్క విలువ "ఉత్పత్తి" అయితే, దీని అర్థం మనం కొత్త పట్టిక ప్రారంభంలో పొరపాట్లు చేసాము, కాబట్టి మేము మునుపటి సెల్ యొక్క విలువను దీనితో ప్రదర్శిస్తాము మేనేజర్ పేరు. లేకపోతే, మేము దేనినీ ప్రదర్శించము, అనగా శూన్యం.

పేరెంట్ సెల్‌ను చివరి పేరుతో పొందడానికి, మేము ముందుగా మునుపటి దశ నుండి పట్టికను సూచిస్తాము #"ఇండెక్స్ జోడించబడింది", ఆపై మనకు అవసరమైన కాలమ్ పేరును పేర్కొనండి [కాలమ్1] చతురస్రాకార బ్రాకెట్లలో మరియు ఆ నిలువు వరుసలోని సెల్ సంఖ్య కర్లీ బ్రాకెట్లలో ఉంటుంది. సెల్ సంఖ్య ప్రస్తుత దాని కంటే ఒకటి తక్కువగా ఉంటుంది, ఇది మనం నిలువు వరుస నుండి తీసుకుంటాము ఇండెక్స్, వరుసగా.

3. ఖాళీ కణాలను పూరించడానికి ఇది మిగిలి ఉంది శూన్య కమాండ్‌తో ఉన్నత కణాల నుండి పేర్లు రూపాంతరం - పూరించండి - డౌన్ (రూపాంతరం — పూరించండి — డౌన్) మరియు మొదటి నిలువు వరుసలో చివరి పేర్లతో సూచికలు మరియు అడ్డు వరుసలతో ఇకపై అవసరం లేని నిలువు వరుసను తొలగించండి. ఫలితంగా, మేము పొందుతాము:

దశ 4. మేనేజర్ల ద్వారా ప్రత్యేక పట్టికలుగా వర్గీకరించడం

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

ఫలితంగా, మేము ప్రతి మేనేజర్ కోసం ప్రత్యేక పట్టికలను పొందుతాము:

దశ 5: నెస్టెడ్ టేబుల్‌లను మార్చండి

ఇప్పుడు మేము ఫలిత నిలువు వరుసలోని ప్రతి సెల్‌లో ఉండే పట్టికలను ఇస్తాము మొత్తం డేటా మంచి ఆకృతిలో.

ముందుగా, ప్రతి పట్టికలో ఇకపై అవసరం లేని నిలువు వరుసను తొలగించండి నిర్వాహకుడు. మేము మళ్ళీ ఉపయోగిస్తాము అనుకూల కాలమ్ టాబ్ ట్రాన్స్ఫర్మేషన్ (రూపాంతరం — అనుకూల కాలమ్) మరియు క్రింది సూత్రం:

అప్పుడు, లెక్కించబడిన మరొక నిలువు వరుసతో, మేము ప్రతి పట్టికలోని మొదటి వరుసను శీర్షికలకు పెంచుతాము:

చివరకు, మేము ప్రధాన పరివర్తనను చేస్తాము - M- ఫంక్షన్‌ని ఉపయోగించి ప్రతి పట్టికను విప్పుతాము Table.UnpivotOtherColumns:

హెడర్ నుండి ప్రాంతాల పేర్లు కొత్త కాలమ్‌లోకి వెళ్తాయి మరియు మేము ఇరుకైన, కానీ అదే సమయంలో, పొడవైన సాధారణ పట్టికను పొందుతాము. ఖాళీ సెల్స్ తో శూన్య విస్మరించబడతాయి.

అనవసరమైన ఇంటర్మీడియట్ నిలువు వరుసలను వదిలించుకోవడం, మేము కలిగి ఉన్నాము:

దశ 6 నెస్టెడ్ టేబుల్‌లను విస్తరించండి

నిలువు వరుస హెడర్‌లో డబుల్ బాణాలు ఉన్న బటన్‌ను ఉపయోగించి అన్ని సాధారణీకరించబడిన సమూహ పట్టికలను ఒకే జాబితాగా విస్తరించడానికి ఇది మిగిలి ఉంది:

… మరియు చివరకు మనం కోరుకున్నది పొందుతాము:

మీరు కమాండ్ ఉపయోగించి ఫలిత పట్టికను ఎక్సెల్కు తిరిగి ఎగుమతి చేయవచ్చు హోమ్ — మూసివేయి మరియు లోడ్ చేయండి — మూసివేయండి మరియు లోడ్ చేయండి… (హోమ్ — మూసివేయి&లోడ్ చేయండి — మూసివేయండి&లోడ్ చేయండి...).

• బహుళ పుస్తకాల నుండి విభిన్న శీర్షికలతో పట్టికలను రూపొందించండి
• ఇచ్చిన ఫోల్డర్‌లోని అన్ని ఫైల్‌ల నుండి డేటాను సేకరిస్తోంది
• పుస్తకంలోని అన్ని షీట్‌ల నుండి డేటాను ఒకే పట్టికలో సేకరిస్తోంది