Calculated Column - Fill Using Text From Another Column

[tonybrogan]

Hi Everyone,   I'm struggling creating a calculated column for my dataset to fix a report issue but I'm now left wondering if it is even possible as I'm only using text fields (nothing numeric).

I've spent hours looking for a solution but struggling. Any help available is greatly appreciated. 

This is my dataset and creating the calculated column is the aim.

DocumentNo	Reference	Employee	Calculated Column
100	ABC		Joe Bloggs
100	ABC	Joe Bloggs	Joe Bloggs
110	ABC		Joe Bloggs
110	ABC		Joe Bloggs
110	ABC		Joe Bloggs
200	DEF		Harry Brown
200	DEF	Harry Brown	Harry Brown
200	DEF		Harry Brown
300	XYZ		Steve Austin
300	XYZ		Steve Austin
300	XYZ		Steve Austin
300	XYZ		Steve Austin
310	XYZ		Steve Austin
310	XYZ	Steve Austin	Steve Austin
310	XYZ		Steve Austin
105	XYZ		Steve Austin
210	XYZ		Steve Austin

 
@HotChilli 

[v-xinruzhu-msft · Accepted solution]

Hi @tonybrogan 

You can try the following solutions.

1.Put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRcnRyBpIKSrE6qCJe+akKTjn56enFEClDDMVEiBiBDXRxdcMq4pFYVFSp4FSUX56HQ7UxWCQiMopSEUN8IsElqWWpCo6lxSWZeTiUGxqYookYoaqJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DocumentNo = _t, Reference = _t, Employee = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DocumentNo", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Reference"}, {{"Count", each List.Max([Employee]), type nullable text}, {"Data", each _, type table [DocumentNo=nullable number, Reference=nullable text, Employee=nullable text]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"DocumentNo", "Employee"}, {"DocumentNo", "Employee"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Data",{"Reference", "DocumentNo", "Employee", "Count"})
in
    #"Reordered Columns"

Output

2.You can create a calculated column

Column =
MAXX (
    FILTER (
        'Table (2)',
        [Reference] = EARLIER ( 'Table (2)'[Reference] )
            && [Employee] <> BLANK ()
    ),
    [Employee]
)

Output

Best Regards!

Yolo Zhu

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

[tonybrogan]

Massive thanks Yolo Zhu, it worked perfectly.

[mlsx4]

Hi @tonybrogan 

This works for me:

let
    Origen = Excel.Workbook(File.Contents("C:\ex.xlsx"), null, true),
    Hoja1_Sheet = Origen{[Item="Hoja1",Kind="Sheet"]}[Data],
    #"Encabezados promovidos" = Table.PromoteHeaders(Hoja1_Sheet, [PromoteAllScalars=true]),
    #"Filas agrupadas" = Table.Group(#"Encabezados promovidos", {"NoReference"}, {{"Data", each Table.FillDown(_,{"Employee"}), type table [Document=number, NoReference=text, Employee=nullable text]}}),
    #"Se expandió Data" = Table.ExpandTableColumn(#"Filas agrupadas", "Data", {"Document", "NoReference", "Employee"}, {"Document", "NoReference.1", "Employee"}),
    #"Columnas quitadas" = Table.RemoveColumns(#"Se expandió Data",{"NoReference"}),
    #"Columnas con nombre cambiado" = Table.RenameColumns(#"Columnas quitadas",{{"NoReference.1", "NoReference"}}),
    #"Filas agrupadas1" = Table.Group(#"Columnas con nombre cambiado", {"NoReference"}, {{"Data", each Table.FillUp(_,{"Employee"}), type table [Document=nullable number, NoReference=nullable text, Employee=nullable text]}}),
    #"Se expandió Data1" = Table.ExpandTableColumn(#"Filas agrupadas1", "Data", {"Document", "NoReference", "Employee"}, {"Document", "NoReference.1", "Employee"}),
    #"Columnas quitadas1" = Table.RemoveColumns(#"Se expandió Data1",{"NoReference.1"}),
    #"Columnas reordenadas" = Table.ReorderColumns(#"Columnas quitadas1",{"Document", "NoReference", "Employee"})
in
    #"Columnas reordenadas"

My procedure is:

• Group by Nºreference but putting in operation: All rows 
• Then, where it puts each_ replace by each Table.FillDown(_,{"Employee"}).
• Expand all the data. You will see, that you still have null values. So, I repeat the procedure, but instead of FillDown in the second step, you put FillUp

Hope I make it clear