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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Solution Authors