Microsoft Fabric Community search for transformdata

Re: Error de Power BI del nuevo SQL Server: una cadena de certificados fue emitida por una autoridad que no es de confianza

Syndicate_Admin — Thu, 25 Sep 2025 21:30:42 GMT

Asegúrese de que el inicio de sesión en PowerBI coincide con la forma en que inicia sesión a través de SSMS, especialmente si inicia sesión con sus credenciales de Windows o credenciales de base de datos.

En PowerBI Desktop, TransformData > Configuración de origen de datos > Elija la conexión y "Editar permisos". En Credenciales, asegúrese de que esté usando el tipo correcto de credenciales. Las conexiones a menudo se establecen de forma predeterminada en "Cuenta de Microsoft", donde iniciaría sesión con su correo electrónico y pasaría por la ruta de OAuth como si estuviera iniciando sesión en sus servicios en la nube.

Si está configurado correctamente, a veces simplemente volver a hacer la configuración de credenciales en PowerBI ayudará a aclarar las cosas y hacer que funcionen.

Re: Power BI Error from new SQL Server - certificate chain was issued by an authority the is not trusted

AMD0791 — Thu, 25 Sep 2025 21:29:27 GMT

Make sure your login in PowerBI matches how you log in via SSMS, especially if you log in with your Windows credentials or database credentials.

In PowerBI desktop, TransformData > Data source Settings > Choose the connection and "Edit Permissions". Under credentials, make sure it's using the right type of credentials. Connections are often defaulted to "Microsoft account" where you'd log in with your email and go through the OAuth pathway like you're logging in to your cloud services.

If it's set up correctly, sometimes just re-doing the credentials setup in PowerBI will help clear things out and get them working.

Re: Type mismatch

ibarrau — Wed, 27 Aug 2025 20:22:37 GMT

Olá. Só para garantir que não haja erros nas transformações, vamos fazer uma verificação rápida. Abra o Power BI Desktop. Acesse queryeditor/transformdata. Abra as tabelas uma a uma, selecionando todas as colunas e filtrando por "Manter Erros". Certifique-se de que não haja nenhuma linha para cada uma. Se não houver linhas de erro em nenhuma tabela, então é algo com o SQL Server e o serviço do Power BI. Caso contrário, pode haver um erro no ETL. Às vezes, o Power BI Desktop exibe avisos após aplicar e atualizar, que indicam erros no serviço.

Espero que ajude.
Resposta traduzida do inglês

Re: semantic model refresh failure due to MashupFlight_SpecificErrorOnAggregatingText

ibarrau — Wed, 13 Aug 2025 20:15:33 GMT

Hi. Is this the something that happened one time and worked again or is it failing now and it just won't work? sometimes the transformations fails. It could be capacity overload, it can be that the data source was working to much when requested, etc. Those failures are one from time to time.

If this is totally failing each time you refresh it in service, then do the following.

To make sure there is no error in the transformations, let's do a check. Open power bi desktop. Go to queryeditor/transformdata. Open the tables one by one selecting all the columns and filtering by "Keep Errors". Make sure there is no row for each one. If there are no error rows at any table, then it's something with mysql and power bi service. Otherwise, you can have an error in the ETL. Sometimes power bi desktop shows warnings after apply and refresh that will be errors in Service.

I hope that helps,

Re: Power BI Service Data Refresh Error (An error happened while reading data from the provider)

ibarrau — Tue, 12 Aug 2025 14:49:33 GMT

Hi. Just to make sure there is no error in the transformations, let's do a quick check. Open power bi desktop. Go to queryeditor/transformdata. Open the tables one by one selecting all the columns and filtering by "Keep Errors". Make sure there is no row for each one. If there are no error rows at any table, then it's something with mysql and power bi service. Otherwise, you can have an error in the ETL. Sometimes power bi desktop shows warnings after apply and refresh that will be errors in Service.

I hope that helps,

Re: How to Split some rows of a table based on hours worked

mromain — Wed, 25 Jun 2025 20:05:56 GMT

Hi somnath6309,

Here a possible solution:

let Source = #table( type table [WorkerId = Int64.Type, Date = date, TimeStart = time, HoursWorked = Int64.Type, Amount = Int64.Type], { {1, #date(2016, 1, 1), #time(9, 0, 0), 8, 160}, {1, #date(2016, 1, 15), #time(18, 0, 0), 6, 180}, {1, #date(2016, 1, 31), #time(21, 0, 0), 9, 360}, {2, #date(2016, 1, 1), #time(9, 0, 0), 8, 160}, {2, #date(2016, 1, 15), #time(18, 0, 0), 5, 150}, {2, #date(2016, 1, 31), #time(21, 0, 0), 8, 320}, {1, #date(2016, 2, 1), #time(9, 0, 0), 4, 80}, {1, #date(2016, 2, 15), #time(18, 0, 0), 3, 90}, {1, #date(2016, 2, 29), #time(21, 0, 0), 8, 320}, {2, #date(2016, 2, 1), #time(9, 0, 0), 6, 120}, {2, #date(2016, 2, 15), #time(18, 0, 0), 5, 150}, {2, #date(2016, 2, 29), #time(21, 0, 0), 8, 320} }), TransformSource = let fnTransformRecord = (r) => let From = DateTime.From(Number.From(r[TimeStart]) + Number.From(r[Date])), To = From + #duration(0, r[HoursWorked],0,0), ListDates = List.Transform({Number.From(Date.From(From)) .. Number.From(Date.From(To)) - (if Time.Hour(To) = 0 then 1 else 0)}, Date.From), baseResult = Record.RemoveFields(r, {"Date", "TimeStart", "HoursWorked"}) in List.Transform(ListDates, each let timeStart = if _ = Date.From(From) then Time.From(From) else #time(0,0,0) in baseResult & [Date = Date.From(_), TimeStart = timeStart, HoursWorked = (if _ = Date.From(To) then Time.Hour(Time.From(To)) else 24) -Time.Hour(timeStart)]), TransformData = Table.FromRecords(List.Combine(List.Transform(Table.ToRecords(Source), fnTransformRecord))), ReorderColumns = Table.ReorderColumns(TransformData,Table.ColumnNames(Source)) in Value.ReplaceType(ReorderColumns, Value.Type(Source)) in TransformSource

Re: Grouping and merging records (across multiple rows) by staff and continuous dates

mromain — Fri, 13 Jun 2025 14:04:42 GMT

Hello everyone,

Here is another solution.
However, following the defined rules, I find 2 rows for James.

let Source = #table( type table [name = text, #"start date" = date, #"end date" = date], { {"James", #date(2015, 11, 12), #date(2017, 11, 11)}, {"James", #date(2019, 5, 30), #date(2020, 7, 3)}, {"James", #date(2020, 7, 3), #date(2033, 3, 21)}, {"Sarah", #date(2019, 5, 30), #date(2033, 7, 3)}, {"Sarah", #date(2020, 7, 3), #date(2032, 3, 21)}, {"Clare", #date(2020, 7, 3), #date(2033, 3, 21)}, {"Simon", #date(2015, 11, 12), #date(2017, 11, 11)}, {"Simon", #date(2021, 8, 3), #date(2023, 9, 3)} }), TransformSource = #table(type table [ID=Int64.Type, data=record], List.Zip({{1 .. Table.RowCount(Source)}, Table.ToRecords(Source)})), fnGetEndDate = (r as record) as date => let nextRecord = try TransformSource{[ID = r[ID]+1]} otherwise null, result = if nextRecord is null then r[data][#"end date"] else if nextRecord[data][name] <> r[data][name] then r[data][#"end date"] else if (nextRecord[data][name] = r[data][name]) and (nextRecord[data][#"start date"] > r[data][#"end date"]) then r[data][#"end date"] else @fnGetEndDate(nextRecord) in result, fnGetStartDate = (r as record) as date => let prevRecord = try TransformSource{[ID = r[ID]-1]} otherwise null, result = if prevRecord is null then r[data][#"start date"] else if prevRecord[data][name] <> r[data][name] then r[data][#"start date"] else if (prevRecord[data][name] = r[data][name]) and (prevRecord[data][#"end date"] < r[data][#"start date"]) then r[data][#"start date"] else @fnGetStartDate(prevRecord) in result, TransformData = Table.FromRecords(Table.TransformRows(TransformSource, each Record.AddField(Record.AddField(Record.AddField(_, "name", [data][name]), "start date", fnGetStartDate(_)), "end date", fnGetEndDate(_)))), RemoveDuplicates = Table.Distinct(TransformData[[name], [#"start date"], [#"end date"]]) in RemoveDuplicates

Re: Table.ReplaceValue + Text.removeRange for mutiple columns

DataNinja777 — Fri, 31 Jan 2025 09:04:10 GMT

Hi informer ,

Yes, it is possible to write a single script to apply the transformation to multiple columns dynamically using Table.TransformColumns in Power Query (M language). Instead of repeating Table.ReplaceValue for each column, you can define a list of target columns and apply the same transformation logic to all of them. The following script accomplishes this by using List.Transform to iterate over each column and applying Text.RemoveRange to remove the acronym:

let Source = #"Add col Nom", ColumnsToTransform = {"Name", "Volume(24h)", "Circulation Supply"}, TransformData = Table.TransformColumns( Source, List.Transform(ColumnsToTransform, each {_, each Text.RemoveRange(_, Text.Length(_) - Text.Length([Acronyme]), Text.Length([Acronyme])), type text } ) ) in TransformData

This method ensures that all specified columns are processed in one step, making the script more efficient and easier to maintain. If additional columns require the same transformation, they can simply be added to the ColumnsToTransform list without modifying the core logic.

Best regards,

No se pudo cambiar la fuente de datos a otra base de datos dentro del mismo servidor

Syndicate_Admin — Sat, 16 Nov 2024 04:19:15 GMT

Intenté cambiar la fuente de datos de un visual (abc.pbix) de una base de datos (Dev) a otra base de datos (Test) que también está en el mismo servidor usando parámetros, pero eso no funcionó. Así que intenté cambiar la fuente de datos manualmente para solucionar los problemas.

Creé un objeto visual ficticio en la base de datos de prueba utilizando una de las tablas de abc.pbix. hizo clic en TransformData, copió el código del paso "Navegación" y lo pegó en el paso Navegación de la misma tabla en adc.pbix en Desarrollo en el editor de Power Query.

Recibí un mensaje de error que indica que "Mydatabase-Test" no se reconoció. Asegúrate de haberlo escrito correctamente".

(Este fue el mismo mensaje que recibí incluso cuando intenté cambiar la fuente de datos a través de parámetros). Pero esta vez ni siquiera escribí el nombre. Lo corté y pegué como se muestra arriba.

Luego intenté hacer lo mismo, copiar / pegar el nombre de la fuente usando el editor avanzado en lugar de en la ventana de Power Query.

Recibí un mensaje que dice "Mydatabase-Test no coincide con ninguna exportación. ¿Te has perdido alguna referencia de módulo?

Tengo que hacer que los informes se prueben en el entorno de prueba pronto. ¿Puede ayudar a resolver o solucionar este problema?

Gracias

Could not change the data source to another DB on the same server

arunbyc — Sat, 16 Nov 2024 04:22:00 GMT

I tried to change the data source of a visual (abc.pbix) from one database ("MyDatabase-Dev") to another database ("MyDatabase-Test") which is also on the same server using Parameters but that did not work. So I tried to change the data source manually to troubleshoot the issues.

I created a dummy visual in the Test database using one of the tables from abc.pbix. clicked TransformData, copied the code from the "Navigation" step, and pasted it on the Navigation step of the same table in adc.pbix on Dev in power query editor.

I got an error message that "MyDatabase-Test" wasn't recognized. Make sure you spelled it correctly".

(This was the same message I received even when I tried changing the data source through parameters). But this time I did not even type the name. I cut and pasted it as above.

I then tried to do the same copy/paste the source name using the advanced editor instead of in the Power Query window.

I received a message that "Mydatabase-Test matches no exports. Did you miss a module reference?

I have to get the Reports to be tested in the Test environment soon. Can you please help how to resolve or troubleshoot this issue?

Thanks

Re: scheduled refresh web page, disabled (You have no personal gateways installed)

Phsc1 — Mon, 04 Mar 2024 17:08:00 GMT

hi
thank you for you input and patience:)
using Google Gemini I managed to build this code
but it fails in line 4 with "Eof Token expected".
marking "=> "

can you see why?

//(* Get data from a paginated website with error handling and transformation *)

// Function definitions for data processing tasks
getPageData = (pageNumber as number) => {
try
Web.Contents(
Text.Combine(source, "?page=", Text.FromNumber(pageNumber)),
[
RelativePath = "/", // Specify relative path within the website (if applicable)
Query = [ // Optional query parameters for filtering (if applicable)
$filter = Text.Combine("HasContent(Data)", "&", "$filter eq 'true'")
]
]
)
otherwise
null
}

errorHandling = (errorMessage as text) =>
Error.Renaming(errorMessage, "Data Extraction Error");

transformData = (data as table) =>
Table.TransformColumns(
data,
{ "Column1", Text.Clean } // Replace "Column1" with actual column names and transformation logic
)

// Variable declarations
let
source = "https://bruun-rasmussen.dk/m/auctions/1007934", // Replace with your base URL
totalPages = 8,

// Combine data from all pages with logging and error handling
allPagesData = List.Accumulate(
List.Range(0, totalPages - 1),
null,
(acc, pageNumber) =>
let
pageData = getPageData(pageNumber)
in
if acc is null then pageData
else Table.Combine({acc, pageData})
)
// Check if any errors occurred during data retrieval
, errorLog = List.Where(allPagesData, isBlank)
// Filter out any null pages resulting from errors
, filteredData = List.RemoveItems(allPagesData, errorLog)

in
// If errors occurred, display them
if List.IsEmpty(errorLog) then
transformData(filteredData)
else
errorHandling(
List.Combine(
"Errors occurred while fetching data from some pages:",
Text.NewLine(),
Text.Combine(" - Page(s): ", Text.Combine(errorLog, ", "))
)
)

Re: crear una nueva tabla a partir de otra con filtros diferentes

Syndicate_Admin — Thu, 01 Feb 2024 05:17:21 GMT

Hii @Syndicate_Admin

Pruebe esto,
En Power Bi, vaya a transformdata y seleccione la tabla. Haga clic con el botón derecho para duplicar la tabla. En la tabla, elija las columnas Tipo y Usarini y, en el panel transfrom anterior, seleccione la opción de columna dinámica para dinamizar la tabla y obtener la solución.

¿He respondido a tu pregunta?

¡Marca mi publicación como una solución, esto ayudará a otros...!

Golpea el kudo también,

Gracias.

Re: Create a new table from another table with different filters

Vallirajap — Thu, 01 Feb 2024 05:16:12 GMT

Hii Syndicate_Admin

Try this,
In power Bi, Go to transformdata and select the table. Right-click to duplicate the table. In the table, choose the Tipo and Usarini columns, and in the above transfrom pane, select the pivot column option to pivot the table to get the solution.

Did I answer your question?

Mark my post as a solution, this will help others...!

Hit the kudo also,

Thank you.

Re: Problema de formato de DirectQuery

Syndicate_Admin — Wed, 24 Jan 2024 07:08:17 GMT

@rwcampbell En Transformdata, duplique la columna de fecha e intente cambiar el tipo de datos a una columna duplicada y use esa columna, supongo que funcionará.
Si te ayuda, márcalo como la solución.
Para ver más escenarios en tiempo real, visite https://powertipstricks.blogspot.com/.
Gracias y saludos.

Re: DirectQuery Formatting problem

PowerBigginer — Wed, 24 Jan 2024 07:07:11 GMT

rwcampbell In Transformdata duplicate the date column and try to change datatype to duplicated column and use that column I guess it will work.
If it helps, please mark it as the Accept it as solution.
For more real-time scenarios, visit https://powertipstricks.blogspot.com/.
Thanks & Regards.

El plegado de consultas está deshabilitado cuando se conecta con databricks sql direct query

Syndicate_Admin — Wed, 05 Jul 2023 07:30:25 GMT

Hola a todos,

Conecté Power BI con puntos de conexión SQL de Databricks mediante la conexión de Azure Databricks. Lo conecté con el modo de consulta directa. No apliqué ningpasos en transformdata (Editor de consultas)... Aún así, el plegado de consultas está deshabilitado. Probé el modo de importación y todavía no puedo ver el plegado de consultas. Por favor sugiera.

Refernce captura de pantalla,

Gracias

Chaitanya

Query folding is disabled when connected with databricks sql direct query

chaitubh — Wed, 05 Jul 2023 07:29:19 GMT

Hello Everyone,

I connected power bi with Databricks SQL end points using Azure Databricks connection. i connected it with direct query mode. i didnt applied anysteps in transformdata(Query Editor)... still the query folding is disabled. I tried import mode also still im not able to see query folding. Please suggest.

Refernce screenshot,

Thanks,

Chaitanya

¿Cómo fusionar 2 conjuntos de datos?

Syndicate_Admin — Thu, 06 Apr 2023 08:00:07 GMT

Querida comunidad,

Por favor, ayuda con los primeros pasos en PowerBi.

Tengo 2 archivos de Excel, que contienen algunos números de dispositivo y sus precios. Ahora quiero comparar estos archivos de Excel.

Este es el contenido del archivo:

Código	Precio
54661-2T410	21,07
54660-2P100	21,47
54661-D3000	25,01
54651-F2010	21,59
54651-2G400	19,89
54651-2T410	21,07
54650-2P100	21,79
55311-3K050	12,13
31111-1R000	8,62
58101-1GE00	10.11
58101-2DA31	10.22

Ahora tengo 2 estos archivos de Excel y necesito comparar las columnas / precios.

Pero la cosa es que el código no es el mismo en ambos excels, faltan números de código en un excel. También hay archivos de código adicionales.

Así que quiero encontrar todo

1) Códigos faltantes

2) códigos adicionales

3) Los mismos códigos -> comparar los precios.

¿Es powerBI una buena herramienta para esta tarea?

Y importé en powerBI y usé TransformData para editar mis datos.

Después de eso me quedé atascado, no sé qué hacer a continuación.

Cualquier idea / sugerencia sería muy útil por favor.

Gracias

Hayk

How to merge 2 data sets?

haykp — Thu, 06 Apr 2023 07:59:00 GMT

Dear Community,

Please help with the first steps in PowerBi.

I have 2 excel files, which contain some device numbers and their prices. Now I want to compare these excel files.

This is the content of the file:

Code	Price
54661-2T410	21,07
54660-2P100	21,47
54661-D3000	25,01
54651-F2010	21,59
54651-2G400	19,89
54651-2T410	21,07
54650-2P100	21,79
55311-3K050	12,13
31111-1R000	8,62
58101-1GE00	10.11
58101-2DA31	10.22

Now I have 2 these excel files and need to compare the columns/prices.

But the thing is that Code is not the same in both excels, there are missing code numbers in one excel. Also there are extra code files.

So I want to find all

1) missing codes

2) extra codes

3) same codes -> compare the prices.

Is powerBI good tool for this task?

And I imported in powerBI, and used TransformData to edit my data.

After that I got stuck, I do not know what to do next.

Any idea/hint would be very helpful please.

Thanks

Hayk

Re: Lost connection to excel file

ibarrau — Mon, 01 Aug 2022 13:53:58 GMT

Hi. Unless you get the Power Bi Desktop file, you won't be able to fix the source. The only way to keep it up to date is using the old folder path.

Once you get the pbix file, you change first on the file. You go to TransformData or Data Source Settings and change the path of the file with the new one. Once you mange that re publish the pbix to Service. The last step would be editing the credentials at Service on the schedule refresh settings.

I hope that helps,