Split multiple Columns into multiple rows

[Bjuu]

Hello everybody,

does anybody knows how to achieve this in PowerQuery?

It would be awsome if somebody could help.

Regards

Bjuu

[Anonymous]

I used this Power Query code to solve a similar problem during a pdf extraction.

FunctionReplace#lf.pbix

Greetings.

[v-frfei-msft]

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

[Bjuu]

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 

[v-frfei-msft]

Hi @Bjuu,

Sorry, I cannot get you, could you please share your excel to me?

Regards,

Frank

[Bjuu]

Hi Frank, i read the Excel wrong and you're absolutely right. Sorry for my late response.

Regards

Bjuu