Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
amaral_diego
Helper II
Helper II

Error to filtering a specific tab of a worksheet using M language in power bi with File in SharePoin

Hello, can you help me with this case below? I'm using M language to do some processing, but I noticed that when I do the processing using M language it seems like it's "duplicating" the values, because I did the same dashboard in the traditional way and it gives me the correct values, but isn't it because it looks at the entire Excel file and not just a specific folder?

Because Excel has two tabs, one called "SubItem" where the dashes are and the other called "Data" where the values ​​I need are, and at no time did I redirect to this specific tab, would it be possible to add this step to the M script? Below is the M script

let
Fonte = SharePoint.Contents("sharepoint address", [ApiVersion = 15]),
#"Documentos Compartilhados" = Fonte{[Name="Documentos Compartilhados"]}[Content],
Controladoria = #"Documentos Compartilhados"{[Name="Controladoria"]}[Content],
#"Outras Colunas Removidas" = Table.SelectColumns(Controladoria,{"Content"}),
#"Personalização Adicionada" = Table.AddColumn(#"Outras Colunas Removidas", "Personalizar", each Excel.Workbook([Content])),
#"Colunas Removidas" = Table.RemoveColumns(#"Personalização Adicionada",{"Content"}),
#"Personalizar Expandido" = Table.ExpandTableColumn(#"Colunas Removidas", "Personalizar", {"Data"}),
#"Personalização Adicionada1" = Table.AddColumn(#"Personalizar Expandido", "PromocaoCabecalhos", each Table.PromoteHeaders([Data])),
#"Colunas Removidas1" = Table.RemoveColumns(#"Personalização Adicionada1",{"Data"}),
#"Erros Removidos" = Table.RemoveRowsWithErrors(#"Colunas Removidas1", {"PromocaoCabecalhos"}),
#"PromocaoCabecalhos Expandido" = Table.ExpandTableColumn(#"Erros Removidos", "PromocaoCabecalhos", {"Mês", "Área", "Mês Abrev", "Descrição Mês", "Data", "Conta Contábil", "Complemento", "Centro de Custo", "Nome Centro de Custo", "Filial Estado", "Centro de Custo Gerencial", "Nome CC", "Conta ", "Descrição da Conta", "Valor", "N3", "Fornecedor", "Tipo", "2024", "Orçamento", "2023", "RF1", "Ano", "Subitem 2024", "Descrição Subitem 2024", "Flex", "Reclassificação/Observação", "Nome CC Alterar", "CC Comunicação"}, {"Mês", "Área", "Mês Abrev", "Descrição Mês", "Data", "Conta Contábil", "Complemento", "Centro de Custo", "Nome Centro de Custo", "Filial Estado", "Centro de Custo Gerencial", "Nome CC", "Conta ", "Descrição da Conta", "Valor", "N3", "Fornecedor", "Tipo", "2024", "Orçamento", "2023", "RF1", "Ano", "Subitem 2024", "Descrição Subitem 2024", "Flex", "Reclassificação/Observação", "Nome CC Alterar", "CC Comunicação"}),
#"Linhas Filtradas" = Table.SelectRows(#"PromocaoCabecalhos Expandido", each [Mês] <> null and [Mês] <> ""),
#"Linhas Superiores Removidas" = Table.Skip(#"Linhas Filtradas",3),
#"Tipo Alterado" = Table.TransformColumnTypes(#"Linhas Superiores Removidas",{{"Mês Abrev", type text}, {"Descrição Mês", type text}, {"Mês", Int64.Type}, {"Data", type date}, {"Conta Contábil", Int64.Type}, {"Centro de Custo", Int64.Type}, {"Nome Centro de Custo", type text}, {"Filial Estado", type text}, {"Área", type text}, {"Centro de Custo Gerencial", Int64.Type}, {"Nome CC", type text}, {"Conta ", Int64.Type}, {"Descrição da Conta", type text}, {"Valor", Currency.Type}, {"N3", type text}, {"Fornecedor", type text}, {"Tipo", type text}, {"2024", Currency.Type}, {"Orçamento", Currency.Type}, {"2023", Currency.Type}, {"RF1", type text}, {"Ano", type text}, {"Subitem 2024", type text}, {"Descrição Subitem 2024", type text}, {"Flex", type text}, {"Reclassificação/Observação", type text}, {"Nome CC Alterar", type text}, {"CC Comunicação", type text}, {"Complemento", type text}})

in
#"Tipo Alterado"

2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

#"Personalização Adicionada" = Table.AddColumn(#"Outras Colunas Removidas", "Personalizar", each Excel.Workbook([Content])),
#"Colunas Removidas" = Table.RemoveColumns(#"Personalização Adicionada",{"Content"}),
#"Personalizar Expandido" = Table.ExpandTableColumn(#"Colunas Removidas", "Personalizar", {"Data"}),

You clicked on the "Combine Tables"  button where instead you should have been choosing the sheet or table that you are interested in.

View solution in original post

Add a filter that chooses the right sheet or table, then address the data via [Content]{0}  (0 is the first row)

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

#"Personalização Adicionada" = Table.AddColumn(#"Outras Colunas Removidas", "Personalizar", each Excel.Workbook([Content])),
#"Colunas Removidas" = Table.RemoveColumns(#"Personalização Adicionada",{"Content"}),
#"Personalizar Expandido" = Table.ExpandTableColumn(#"Colunas Removidas", "Personalizar", {"Data"}),

You clicked on the "Combine Tables"  button where instead you should have been choosing the sheet or table that you are interested in.

Hi, can you assist me here, what should I do on this case?

Add a filter that chooses the right sheet or table, then address the data via [Content]{0}  (0 is the first row)

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.