Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Mr_Stern
Frequent Visitor

Using Data to create a file of original/unique entries

I am not sure if this is accurate under Power Query area or not.

I need to create a file/report of first entry (based on a unique identifier), accounting for a few exceptions.

The data comes from a weekly data dump that will be pulled into a BI Power Query file.

The same ID's will appear each week, and even multiple times in each week.

 

Original Listing.JPGInformation.JPG

REPORT TO BUILD

PayrollCandidate IDWorkerNameWorker_OccupationRateDescriptionGradeCodeDate Added
AHP28399VJohn HenryAllied - PhlebPhlebotomist 202101
Alliance62332VKilgore TroutAmbulance ControllerCALL TAKER  SA 18INC01 M-F Day 202102
AHP62730VDame Edna EverageAmbulance ControllerCALL TAKER  NSA 18INC01 M-F Da 202102
   To Add with Period 202104   
Alliance65431VKilgore TroutVaccinatorVaccinator-S-S Temp 202104
AHP63115VVincent Van GoghNurseRGN DOHC 18INCLSI M-F Day      202104

 

DATA

SourceSystemEmployer_RefWorker_RefWorkerNameWorker_OccupationPeriodCodeTimesheet_NumberRateDescription
SaturnAHP28399VJohn HenryAllied - Phleb2021011Phlebotomist
QuantumAlliance62332VKilgore Trout 2021017 
SaturnAHP62730VDame Edna Everage 2021017 
SaturnAlliance62332VKilgore TroutAmbulance Controller20210215CALL TAKER  SA 18INC01 M-F Day
SaturnAHP62730VDame Edna EverageAmbulance Controller20210228CALL TAKER  NSA 18INC01 M-F Da
SaturnAHP28399VJohn HenryAllied - Phleb20210335Phlebotomist
SaturnAlliance65431VKilgore TroutVaccinator20210473Vaccinator-S-S Temp
SaturnAlliance62332VKilgore TroutAmbulance Controller20210473CALL TAKER  SA 18INC01 M-F Day
SaturnAHP63115VVincent Van GoghNurse202104102RGN DOHC 18INCLSI M-F Day     
1 ACCEPTED SOLUTION
Vera_33
Resident Rockstar
Resident Rockstar

Hi @Mr_Stern 

 

Here is one way, you need to take care of your manual column

output

Vera_33_2-1627353499098.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY7BCoJAEIZfZfCcoLtgelzMsDSJii7SYc1BhXUXNg18m56lJ2vXLkWXTjP/z//PN2XpsHTvLBwS0ig6m2WrWgkpSj0ZwYTosAYX9q3AyhjzVIPqu9tgpC16xPd857Io5zSXVzRuQCgl9lzWiUZphJNWo22wvhqFDUGs5KCVEKiNHbM8hxPLksPzAUcGfrgpYs+HnbuGFZ8+UOSNmp8OyJJ6lrLiPUJSSw7JHTVv8E9S8YP6Jl1e", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Payroll = _t, #"Candidate ID" = _t, WorkerName = _t, Worker_Occupation = _t, RateDescription = _t, GradeCode = _t, #"Date Added" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Payroll", type text}, {"Candidate ID", type text}, {"WorkerName", type text}, {"Worker_Occupation", type text}, {"RateDescription", type text}, {"GradeCode", type text}, {"Date Added", Int64.Type}}),
    originTable = Table.RemoveColumns(#"Changed Type",{"GradeCode"}),
    Custom1 = List.Union({ Table.ToRows( originTable), Table}),
    Custom2 = Table.FromRows(Custom1, Table.ColumnNames(originTable)),
    #"Added Custom" = Table.AddColumn(Custom2, "GradeCode", each null)
in
    #"Added Custom"

 

 

the DATA called Table

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rVHbbsIwDP0Vq89UahLK5bFqGWWwjlHUF9SHABFUSpMppJP4G76FL1syVgSsEmOaolh2bJ9j5ywWTkp1pYTTcoJ4aizukX4/M86z3AqImVB7m+O8YGtwYbrlbGnLPIw8ZBx7vx6llmWx007eWjhvFRW6Kr8bqVgx43YwIdgijwu+kYrBXMlKm/gSrmtjC3EzVgd3iWebI1oyGKwFhcEHU3TD7gP8YoSgXFbcFkEohVaSc6ZqVGy39I0Jg8kE5sF4MDseIA0A9UZJ6CF4cZ8govuHx75Hinu3pMkP1ibSRyQkxiF+k4aN/+e3CWr6v4yuVoWgWp4XaFstyFXKTd0U5qx8/199zkx/0Ycg5FvCrDDgQkNGBQzlZmuekkrt2CXJSZXZMIHoNQ5P8JN0VOMfD/Vx8vwT", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SourceSystem = _t, Employer_Ref = _t, Worker_Ref = _t, WorkerName = _t, Worker_Occupation = _t, PeriodCode = _t, Timesheet_Number = _t, RateDescription = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SourceSystem", type text}, {"Employer_Ref", type text}, {"Worker_Ref", type text}, {"WorkerName", type text}, {"Worker_Occupation", type text}, {"PeriodCode", Int64.Type}, {"Timesheet_Number", Int64.Type}, {"RateDescription", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Employer_Ref", "Worker_Ref", "WorkerName", "Worker_Occupation",  "RateDescription","PeriodCode"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Worker_Occupation] <> "")),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Employer_Ref", "Worker_Ref", "WorkerName", "Worker_Occupation"}),
    Custom1 = Table.ToRows( #"Removed Duplicates")
in
    Custom1

 

 

 

View solution in original post

1 REPLY 1
Vera_33
Resident Rockstar
Resident Rockstar

Hi @Mr_Stern 

 

Here is one way, you need to take care of your manual column

output

Vera_33_2-1627353499098.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY7BCoJAEIZfZfCcoLtgelzMsDSJii7SYc1BhXUXNg18m56lJ2vXLkWXTjP/z//PN2XpsHTvLBwS0ig6m2WrWgkpSj0ZwYTosAYX9q3AyhjzVIPqu9tgpC16xPd857Io5zSXVzRuQCgl9lzWiUZphJNWo22wvhqFDUGs5KCVEKiNHbM8hxPLksPzAUcGfrgpYs+HnbuGFZ8+UOSNmp8OyJJ6lrLiPUJSSw7JHTVv8E9S8YP6Jl1e", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Payroll = _t, #"Candidate ID" = _t, WorkerName = _t, Worker_Occupation = _t, RateDescription = _t, GradeCode = _t, #"Date Added" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Payroll", type text}, {"Candidate ID", type text}, {"WorkerName", type text}, {"Worker_Occupation", type text}, {"RateDescription", type text}, {"GradeCode", type text}, {"Date Added", Int64.Type}}),
    originTable = Table.RemoveColumns(#"Changed Type",{"GradeCode"}),
    Custom1 = List.Union({ Table.ToRows( originTable), Table}),
    Custom2 = Table.FromRows(Custom1, Table.ColumnNames(originTable)),
    #"Added Custom" = Table.AddColumn(Custom2, "GradeCode", each null)
in
    #"Added Custom"

 

 

the DATA called Table

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rVHbbsIwDP0Vq89UahLK5bFqGWWwjlHUF9SHABFUSpMppJP4G76FL1syVgSsEmOaolh2bJ9j5ywWTkp1pYTTcoJ4aizukX4/M86z3AqImVB7m+O8YGtwYbrlbGnLPIw8ZBx7vx6llmWx007eWjhvFRW6Kr8bqVgx43YwIdgijwu+kYrBXMlKm/gSrmtjC3EzVgd3iWebI1oyGKwFhcEHU3TD7gP8YoSgXFbcFkEohVaSc6ZqVGy39I0Jg8kE5sF4MDseIA0A9UZJ6CF4cZ8govuHx75Hinu3pMkP1ibSRyQkxiF+k4aN/+e3CWr6v4yuVoWgWp4XaFstyFXKTd0U5qx8/199zkx/0Ycg5FvCrDDgQkNGBQzlZmuekkrt2CXJSZXZMIHoNQ5P8JN0VOMfD/Vx8vwT", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SourceSystem = _t, Employer_Ref = _t, Worker_Ref = _t, WorkerName = _t, Worker_Occupation = _t, PeriodCode = _t, Timesheet_Number = _t, RateDescription = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SourceSystem", type text}, {"Employer_Ref", type text}, {"Worker_Ref", type text}, {"WorkerName", type text}, {"Worker_Occupation", type text}, {"PeriodCode", Int64.Type}, {"Timesheet_Number", Int64.Type}, {"RateDescription", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Employer_Ref", "Worker_Ref", "WorkerName", "Worker_Occupation",  "RateDescription","PeriodCode"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Worker_Occupation] <> "")),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Employer_Ref", "Worker_Ref", "WorkerName", "Worker_Occupation"}),
    Custom1 = Table.ToRows( #"Removed Duplicates")
in
    Custom1

 

 

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.