Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 |
Solved! Go to 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.
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.
Massive thanks Yolo Zhu, it worked perfectly.
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:
Hope I make it clear
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.