Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everybody,
does anybody knows how to achieve this in PowerQuery?
It would be awsome if somebody could help.
Regards
Bjuu
I used this Power Query code to solve a similar problem during a pdf extraction.
Greetings.
Hi @Bjuu,
Based on my test, we can use the Fill down function in query editor.
1. Enter the data in excel and import data from the excel file.
2. Use the fill down function as below, then we can get the result as we need.
Here is the code in the advanced editor.
let Source = Excel.Workbook(File.Contents("filepath\123.xlsx"), null, true), Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Contract", type text}, {"Type1", type text}, {"Costtype1", type text}, {"Cost1", Int64.Type}, {"Costtype2", type text}, {"Cost2", Int64.Type}}), #"Filled Down" = Table.FillDown(#"Changed Type",{"ID", "Contract", "Type1"}) in #"Filled Down"
Regards,
Frank
Hi Frank,
thanks for your reply.
Your solution would be perfect if each cell in Excel-Source wouldn't include a #(lf). That's the Problem i'm dealing with. I can split columns only one column at a time and that causes the issue, that the generated rows include duplicates of the other rows. Hope you understand what i mean.
Regards,
Bjuu
Hi @Bjuu,
Sorry, I cannot get you, could you please share your excel to me?
Regards,
Frank
Hi Frank, i read the Excel wrong and you're absolutely right. Sorry for my late response.
Regards
Bjuu
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |