Seperate column in PowerQuery

[santnok]

Hi
I have a tabel in power query excel 2016. In this query I have a tabel like this under here.
My question is, is it posible to split the A column so it will look like the second tabel under here?
In my tabel I have many other coulums to, like Amount, Restamount and som others, but this is not important in what I trying to do.

Tabel 1 Raw data in

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10018 Customer name[/TD]
[TD]<-- Customer number and name[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2089[/TD]
[TD]<-- Invoice number[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10019 Customer name[/TD]
[TD]<-- Customer number and name[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2000344[/TD]
[TD]<-- Invoice number[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2000345[/TD]
[TD]<-- Invoice number[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1001 Customer name[/TD]
[TD]<-- Customer number and name[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]345[/TD]
[TD]<-- Invoice number[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]67598[/TD]
[TD]<-- Invoice number[/TD]
[/TR]
</tbody>[/TABLE]


Tabel 2 Hope to get the result like this.

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD][/TD]
[TD]A (Customer number)[/TD]
[TD]B (Customer name)[/TD]
[TD]C (Invoice number)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10018[/TD]
[TD]Customer name[/TD]
[TD]2089[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10019[/TD]
[TD]Customer name[/TD]
[TD]2000344[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10019[/TD]
[TD]Customer name[/TD]
[TD]2000345[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1001[/TD]
[TD]Customer name[/TD]
[TD]345[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1001[/TD]
[TD]Customer name[/TD]
[TD]67598[/TD]
[/TR]
</tbody>[/TABLE]


Hope someone can help me out here

 

[sandy666]

in your data one customer name has different customer number

so I modified it a bit

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]src[/td][td][/td][td=bgcolor:#70AD47]Cust. #[/td][td=bgcolor:#70AD47]Cust. name[/td][td=bgcolor:#70AD47]invoice #[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]10018 Customer name1[/td][td][/td][td=bgcolor:#E2EFDA]10018[/td][td=bgcolor:#E2EFDA]Customer name1[/td][td=bgcolor:#E2EFDA]2089[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

2089​

[/td][td][/td][td]10019[/td][td]Customer name2[/td][td]2000344[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]10019 Customer name2[/td][td][/td][td=bgcolor:#E2EFDA]10019[/td][td=bgcolor:#E2EFDA]Customer name2[/td][td=bgcolor:#E2EFDA]2000345[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

2000344​

[/td][td][/td][td]1001[/td][td]Customer name3[/td][td]345[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

2000345​

[/td][td][/td][td=bgcolor:#E2EFDA]1001[/td][td=bgcolor:#E2EFDA]Customer name3[/td][td=bgcolor:#E2EFDA]67598[/td][/tr]

[tr=bgcolor:#FFFFFF][td]1001 Customer name3[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

345​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

67598​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table11"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"src", type any}}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type", "Text Before Delimiter", each Text.BeforeDelimiter(Text.From([src], "en-GB"), " "), type text),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Before Delimiter", "Text After Delimiter", each Text.AfterDelimiter(Text.From([src], "en-GB"), " "), type text),
    #"Replaced Value" = Table.ReplaceValue(#"Inserted Text After Delimiter","",null,Replacer.ReplaceValue,{"Text After Delimiter"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Text After Delimiter"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Text After Delimiter"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Distinct(Table.Column([Count],"Text Before Delimiter"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Custom.1", "Custom.2"}),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Split Column by Delimiter", {{"Custom.2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom.2"),
    #"Reordered Columns" = Table.ReorderColumns(#"Split Column by Delimiter1",{"Count", "Custom.1", "Text After Delimiter", "Custom.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Count"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.1", "Cust. #"}, {"Text After Delimiter", "Cust. name"}, {"Custom.2", "invoice #"}})
in
    #"Renamed Columns"		[/SIZE]

 

[santnok]

Hi sandy666
Thank you verry much for the code.
Is it to much to ask if you can make a step guide for this. Have and exsisting tabel in power query that I have to change. Have tryid to understand out from the code, but I'm not so steady.

Thanks

 

[sandy666]

blue table is a simple table from Ctrl+T (Table11) (in Name Manager check it and change to Table11 if necessary)
I changed customer name by adding a number to make them different: 1, 2, 3
then From Table and in Advanced Editor replace all there with code from the post.
then you will see steps in Query Settings - Applied Steps

let me know if it works for you

btw. it doesn't matter where you change the table name, they must be the same in Name Manage and in the code

 

[santnok]

Thank you verry much for the help
I have now recreated my query and I also understand (almost) what you have done.

 

[sandy666]

I also understand (almost) what you have done.

almost not means full

maybe it will help: example excel file