Identify New Rows, Common Rows and Original Rows

h_gp9
Posts: 17
Joined: Wed Jan 18, 2017 9:50 am

Identify New Rows, Common Rows and Original Rows

Postby h_gp9 » Sat Mar 18, 2017 6:01 am

Hello to everybody:

We have Table Actual and Table Original.
I need to create a Hybrid Table with this 2 tables mentioned but I also need identify what rows are New, Common and Original in a column.
How to achieve this with power query.?
Thanks in advanced.

mitchandsuzy
Posts: 18
Joined: Tue Nov 17, 2015 4:00 pm

Re: Identify New Rows, Common Rows and Original Rows

Postby mitchandsuzy » Thu Mar 23, 2017 12:05 pm

Hi H,
Matt showed us a way to do this at one of our User Group meetings.

Code: Select all

let
    SolutionPath = fnGetParameter("File Path"),      <---- this just gets the current directory 
    Source = Folder.Files(SolutionPath),
    #"Lowercased Text" = Table.TransformColumns(Source,{{"Name", Text.Lower}}),   <----    PQ is case sensitive
    #"Filtered Rows" = Table.SelectRows(#"Lowercased Text", each Text.EndsWith([Name],"1060.xlsx" )),   
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Name"}),
 

Text.EndsWith([Name],"1060.xlsx" <---- This is the way to select all the files you want to combine
---> At this point you will have a list of the files you want to join together with Content Binary, and Name with all the file names

Code: Select all

    con't   
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Name", Order.Descending}}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Sorted Rows", "Custom", {"Name", "Data", "Item", "Kind"}, {"Custom.Name", "Data", "Item", "Kind"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each [Kind] = "Sheet"),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows1",{"Name", "Custom.Name", "Data"}),

---> By this stage you will have 3 cols one with the filename, one with the name of the sheet and the data col for expansion

Code: Select all

    con't
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns1", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7",....}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data"),   ---> provided your data has the headers on the 1st line...
    #"Removed Columns3" = Table.RemoveColumns(#"Promoted Headers",{"Sheet1", "Column21",... unwanted columns}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns3",{{Table.ColumnNames(#"Removed Columns3"){0}, "Source File"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Source File", type text}, ...as desired}),
    #"Filtered Rows2" = Table.SelectRows(#"Changed Type", each ([Project Definition] <> "Project Definition")),  <--- this just gets rid of the duplicate row 1 from each file in your list


At this point you will have all the columns you want plus a column with the name of the file that the data came from.

I hope this helps

Suzy

h_gp9
Posts: 17
Joined: Wed Jan 18, 2017 9:50 am

Re: Identify New Rows, Common Rows and Original Rows

Postby h_gp9 » Fri Mar 24, 2017 1:33 pm

Could you please send me your 1060.xlsx file?
I'm not a programmer only I'm an accountant :P
Thanks in advanced.

mitchandsuzy
Posts: 18
Joined: Tue Nov 17, 2015 4:00 pm

Re: Identify New Rows, Common Rows and Original Rows

Postby mitchandsuzy » Tue Mar 28, 2017 10:37 am

Hi H,

I would take me ages to sanitize my data to send to you.

However if you would like to send me a sample of your files I will add the code into file for you and you can step through the code yourself to see how it works.

Suzy

h_gp9
Posts: 17
Joined: Wed Jan 18, 2017 9:50 am

Re: Identify New Rows, Common Rows and Original Rows

Postby h_gp9 » Thu Mar 30, 2017 4:49 am

I'm sending two txt files "Actual" & "Last" that I want to convert in just one "New".
This is that I need:
1.-"Actual" should be the Base and will be complemented with appended rows totally different from "Last"
2.-In the new file mixed "New" there will be an extra column used as identifier the source of this rows. For example "C" if existe in both (Common) and "L" if being of "Last".

Thank you very much.
Attachments
Last.zip
(694.53 KiB) Downloaded 23 times
Actual.zip
(709.51 KiB) Downloaded 20 times

mitchandsuzy
Posts: 18
Joined: Tue Nov 17, 2015 4:00 pm

Re: Identify New Rows, Common Rows and Original Rows

Postby mitchandsuzy » Thu Mar 30, 2017 10:51 pm

Hi H,

Please find attached file. Just put the two files in the same directory as the excel file and go into power pivot and you will see what I think you wanted.

Suzy
Attachments
Actual and last.xlsx
(973.35 KiB) Downloaded 22 times

h_gp9
Posts: 17
Joined: Wed Jan 18, 2017 9:50 am

Re: Identify New Rows, Common Rows and Original Rows

Postby h_gp9 » Sat Jun 17, 2017 12:53 am

Hi Mitchandsuky:

When I try to run power pivot I get errors that I send to you in as attachment.
Could you help me please? thanks in advanced.
Attachments
message error.docx
(553.04 KiB) Downloaded 11 times


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 2 guests