Extract data problem,the data is not position in right columns

[Anonymous]

The result look like 

Name	number	Room
		213452  A
Name	number	Room
		217452 A
Name	number	Room
		243452 A

The result i want like that

Name	number	Room
	213452	A
Name	number	Room
	217452	A
Name	number	Room
	243452	A

i want to it can auto fixed it instead of changing it manually every times

[V-lianl-msft · Accepted solution]

Hi @Anonymous,

It can be visualized in the query editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlDSAWMjQ2MTUyMFBUelWJ1oJb/E3FSgYF5pblJqEZARlJ+fqwCWQag3B6knWrmJMVR5LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, number = _t, #"Room " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"number", type text}, {"Room ", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [number] = "number" then [number] else [#"Room "]),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Added Conditional Column", "Custom", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Custom.1", "Custom.2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Character Transition","","",Replacer.ReplaceValue,{"Custom.2"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," ","",Replacer.ReplaceText,{"Custom.2"}),
    #"Added Conditional Column1" = Table.AddColumn(#"Replaced Value1", "Custom", each if [Custom.2] = null then [#"Room "] else [Custom.2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"number", "Room ", "Custom.2"})
in
    #"Removed Columns"

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

In the query editor, just delete the name column and rename the number column to Namenumber (if I read your post correctly).  Everytime you refresh, it will automatically do those same steps when it loads the data.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[vanessafvg]

the result when you combine it in power query? how are you extracting it? can you share what you are doing