Importar dados do OneDrive e do SharePoint para o Power Query/BI

Se você ou sua empresa armazenam dados na nuvem do OneDrive ou em um portal da empresa do SharePoint, conectar-se diretamente a ele usando o Power Query no Excel ou no Power BI pode ser surpreendentemente desafiador.

Quando enfrentei um problema semelhante, fiquei surpreso ao descobrir que não há maneiras “legais” de resolvê-lo. Por algum motivo, a lista de fontes de dados disponíveis no Excel e até mesmo no Power BI (onde o conjunto de conectores é tradicionalmente mais amplo) por algum motivo não inclui a capacidade de se conectar a arquivos e pastas do OneDrive.

Portanto, todas as opções oferecidas abaixo são, de uma forma ou de outra, “muletas” que exigem um pequeno mas manual “acabamento com um arquivo”. Mas essas muletas têm uma grande vantagem – elas funcionam 🙂

Qual é o problema?

Uma breve introdução para quem passou os últimos 20 anos em coma não no assunto.

OneDrive é um serviço de armazenamento em nuvem da Microsoft que vem em vários sabores:

• OneDrive Pessoal – para usuários comuns (não corporativos). Eles oferecem 5 GB de graça + espaço extra por uma pequena taxa mensal.
• Onedrive for Business – uma opção para usuários corporativos e assinantes do Office 365 com um volume disponível muito maior (de 1 TB ou mais) e recursos adicionais, como armazenamento de versão, etc.

Um caso especial do OneDrive for Business é o armazenamento de dados em um portal corporativo do SharePoint – nesse cenário, o OneDrive é, de fato, uma das bibliotecas do SharePoint'a.

Os arquivos podem ser acessados ​​por meio da interface da Web (site https://onedrive.live.com ou site corporativo do SharePoint) ou sincronizando pastas selecionadas com seu PC:

Normalmente, essas pastas são armazenadas no perfil do usuário na unidade C - o caminho para elas se parece com C: UsuáriosNome de Utilizadoronedrive). Um programa especial monitora a relevância dos arquivos e a sincronização de todas as alterações – АAgente do OneDrive (nuvem azul ou cinza no canto inferior direito da tela):

E agora o principal.

Se precisarmos carregar dados do OneDrive para o Excel (via Power Query) ou para o Power BI, é claro que podemos especificar arquivos e pastas locais a serem sincronizados como fonte da maneira usual via Obter dados – Do arquivo – Do livro / Da pasta (Obter dados — do arquivo — da pasta de trabalho/pasta)BUT não será um link direto para a nuvem do OneDrive.

Ou seja, no futuro, ao alterar, por exemplo, arquivos na nuvem por outros usuários, precisa sincronizar primeiro (isso acontece por um longo tempo e nem sempre é conveniente) e apenas em seguida, atualize nossa consulta Power Query ou Modelo no Power BI.

Naturalmente, surge a pergunta: como importar dados do OneDrive/SharePoint diretamente para que os dados sejam carregados diretamente da nuvem?

Opção 1: conectar-se a um livro do OneDrive for Business ou SharePoint

1. Abrimos o livro em nosso Excel – uma cópia local da pasta sincronizada do OneDrive como um arquivo comum. Ou abra o site primeiro no Excel Online e depois clique no botão Abra no Excel (Abrir no Excel).
2. Acesse Arquivo - Detalhes (Informações do arquivo)
3. Copie o caminho da nuvem para o livro com o botão caminho de cópia (Copiar caminho) no título:

   

4. Em outro arquivo do Excel ou no Power BI, onde você deseja preencher os dados, selecione os comandos Obter dados – Da Internet (Obter dados — da web) e cole o caminho copiado no campo de endereço.
5. Excluir no final do caminho ?web=1 e clique em OK:

   

6. Na janela que aparece, selecione o método de autorização Conta da organização (Conta da organização) e clique no botão Entrar (Conecte-se):

   

   Digite nossa senha de login ou selecione uma conta corporativa na lista exibida. Se você fizer tudo certo, então a inscrição Entrar deve mudar para Entre com um usuário diferente (Faça login com outra conta de usuário).
   

7. Clique no botão da conexão (Conectar).
   

Então tudo é o mesmo que com a importação usual de um livro - selecionamos as planilhas necessárias, tabelas inteligentes para importação, etc.

Opção 2: conectar-se a um arquivo do OneDrive Personal

Para se conectar a um livro em uma nuvem OneDrive pessoal (não corporativa), a abordagem será diferente:

1. Abrimos o conteúdo da pasta desejada no site do OneDrive e encontramos o arquivo importado.
2. Clique com o botão direito sobre ele e selecione o comando Introdução (Embutir) ou selecione o arquivo e selecione um comando semelhante no menu superior:

   

3. No painel que aparece à direita, clique no botão Crie e copie o código gerado:

   

4.  Cole o código copiado no Bloco de Notas e “terminar com um arquivo”:
   
   • Remova tudo, exceto o link entre aspas
     
   • Excluir o bloco cid=XXXXXXXXXXXX&
   • Palavra substituível embutir on download
   Como resultado, o código-fonte deve ficar assim:

   

5. Então tudo é o mesmo que no método anterior. Em outro arquivo do Excel ou no Power BI, onde você deseja preencher os dados, selecione os comandos Obter dados – Da Internet (Obter dados — da web), cole o caminho editado no campo de endereço e clique em OK.
6. Quando a janela de autorização aparecer, selecione a opção Windows e, se necessário, insira a senha de login do OneDrive.
   

Opção 3: Importar o conteúdo de uma pasta inteira do OneDrive for Business

Se você precisar preencher no Power Query ou no Power BI o conteúdo não de um arquivo, mas de uma pasta inteira de uma só vez (por exemplo, com relatórios), a abordagem será um pouco mais simples:

1. No Explorer, clique com o botão direito do mouse na pasta sincronizada local de seu interesse no OneDrive e selecione Ver no local (Ver online).
2. Na barra de endereços do navegador, copie a parte inicial do endereço – até a palavra / _layouts:

   

3. Na pasta de trabalho do Excel onde você deseja carregar os dados ou no relatório do Power BI Desktop, selecione os comandos Obter dados – do arquivo – da pasta do SharePoint (Obter dados — do arquivo — da pasta do SharePoint):

   

   Em seguida, cole o fragmento do caminho copiado no campo de endereço e clique em OK:

   

   Se uma janela de autorização for exibida, selecione o tipo conta da Microsoft (Conta Microsoft), clique no botão Entrar (Conecte-se), e então, após um login bem-sucedido, no botão da conexão (Conectar):

   

4. Depois disso, todos os arquivos do SharePoint são solicitados e baixados e uma janela de visualização aparece, onde você pode clicar com segurança em Converter dados (Transformar dados).
   
5. A edição adicional da lista de todos os arquivos e sua mesclagem já ocorre no Power Query ou no Power BI de maneira padrão. Para restringir o círculo de pesquisa apenas à pasta que precisamos, você pode usar o filtro por coluna Caminho da pasta (1) e depois expanda todo o conteúdo dos arquivos encontrados usando o botão na coluna Conteúdo (2)

   

• Montando tabelas de arquivos diferentes usando o Power Query
• O que é Power Query, Power Pivot, Power BI e como eles podem ajudar você
• Coletando dados de todas as folhas do livro em uma tabela