Wani lokaci akwai yanayi lokacin da ba a sani ba a gaba daidai adadin da kuma waɗanne layuka ne ake buƙatar shigo da su daga bayanan tushen. A ce dole ne mu loda bayanai daga fayil ɗin rubutu zuwa Power Query, wanda, da farko kallo, ba ya gabatar da babbar matsala. Wahalar ita ce, ana sabunta fayil ɗin akai-akai, kuma gobe yana iya samun layukan daban-daban tare da bayanai, taken guda uku, ba layi biyu ba, da sauransu.
Wato ba za mu iya faɗi gaba da tabbaci ba, farawa daga wane layi da daidai adadin layukan da ake buƙatar shigo da su. Kuma wannan matsala ce, saboda waɗannan sigogi suna da hard-coded a cikin M-code na buƙatar. Kuma idan kun yi buƙatun fayil ɗin farko (shigo da layukan 5 da suka fara daga na 4), to ba zai ƙara yin aiki daidai da na biyun ba.
Zai yi kyau idan tambayar mu da kanta zata iya tantance farkon da ƙarshen toshe rubutun "mai iyo" don shigo da kaya.
Maganin da nake so in ba da shawara ya dogara ne akan ra'ayin cewa bayananmu sun ƙunshi wasu kalmomi ko dabi'u waɗanda za a iya amfani da su azaman alamomi (fasali) na farkon da ƙarshen bayanan da muke buƙata. A cikin misalinmu, farkon zai zama layin da zai fara da kalmar SKU, kuma ƙarshen layi ne tare da kalmar Jimlar. Wannan ingantaccen layin yana da sauƙin aiwatarwa a cikin Tambayar Wuta ta amfani da ginshiƙi na sharadi - analogue na aikin IF (IF) Daga Microsoft Excel.
Bari mu ga yadda za a yi.
Da farko, bari mu ɗora abubuwan da ke cikin fayil ɗin rubutun mu cikin Tambayoyin Wuta a daidaitacciyar hanya – ta hanyar umarni Bayanai - Sami bayanai - Daga fayil - Daga rubutu/fayil CSV (Bayani - Samun Bayanai - Daga fayil - Daga rubutu / fayil CSV). Idan kuna shigar da Query Query azaman ƙarawa daban, to, umarni masu dacewa zasu kasance akan shafin Tambayar .arfi:
Kamar koyaushe, lokacin da ake shigo da kaya, zaku iya zaɓar halayen mai raba shafi (a cikin yanayinmu, wannan shafin ne), kuma bayan an shigo da shi, zaku iya cire matakin da aka ƙara ta atomatik. nau'in gyarawa (An Canja Nau'in), saboda ya yi da wuri a gare mu mu sanya nau'ikan bayanai zuwa ginshiƙai:
Yanzu tare da umarnin Ƙara Shagon - Sharadi na Sharadi (Ƙara Shagon - Sharadi na Sharadi)bari mu ƙara ginshiƙi tare da bincika yanayi guda biyu - a farkon da ƙarshen toshe - da nuna kowane ƙima daban-daban a cikin kowane hali (misali, lambobi 1 и 2). Idan babu ɗayan sharuɗɗan da aka cika, to fitarwa null:
Bayan danna kan OK muna samun hoton kamar haka:
Yanzu bari mu je shafin. Sake Kama kuma zaɓi ƙungiya Cika - Kasa (Canza - Cika - Kasa) - namu da biyu za su shimfiɗa ginshiƙi:
Da kyau, to, kamar yadda zaku iya tsammani, zaku iya kawai tace raka'a a cikin ginshiƙi na sharadi - kuma ga yanki na kwaɗayinmu:
Abin da ya rage shine a ɗaga layin farko zuwa taken tare da umarnin Yi amfani da layin farko azaman masu kai tab Gida (Gida - Yi amfani da Layi na Farko azaman masu kai) kuma cire ginshiƙin da ba dole ba ta hanyar danna dama akan taken sa da zaɓin umarni Share shafi (Goge Shagon):
An warware matsalar. Yanzu, lokacin da ake canza bayanai a cikin fayil ɗin rubutu na tushe, tambayar yanzu za ta ƙayyade farkon da ƙarshen ɓangaren “masu iyo” na bayanan da muke buƙata kuma a shigo da daidai adadin layukan kowane lokaci. Tabbas, wannan tsarin yana aiki a cikin yanayin shigo da XLSX, ba fayilolin TXT ba, da kuma lokacin shigo da duk fayiloli daga babban fayil lokaci guda tare da umarnin. Bayanai - Sami bayanai - Daga fayil - Daga babban fayil (Data - Sami Data - Daga fayil - Daga babban fayil).
- Haɗa tebur daga fayiloli daban-daban ta amfani da Query Query
- Sake tsara ma'aunin giciye zuwa lebur tare da macros da Query Query
- Gina Taswirar Gantt Project a cikin Query Query