Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Create new column from Data coming from Different columns

Hi Experts,    I would like to create a column in power query picking up data from other columns on which some columns the Data format is ready but for other columns I have to use search/find formu...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
4 years ago

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Job", type text}, {"Job1", type text}, {"Job2", type text}, {"Job3", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Job3", type text}}, "en-IN"),{"Job", "Job1", "Job2", "Job3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Added Custom" = Table.AddColumn(#"Merged Columns", "Custom", each if Text.Start([Merged],2)="LU" then [Merged] else if Value.Is(Value.FromText([Merged]), type number) then "LU"&[Merged] else Text.BetweenDelimiters([Merged], "#", "-"))
in
    #"Added Custom"

Hope this helps.