Page 1 of 1

Identify New Rows, Common Rows and Original Rows

Posted: Sat Mar 18, 2017 6:01 am
by h_gp9
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.

Re: Identify New Rows, Common Rows and Original Rows

Posted: Thu Mar 23, 2017 12:05 pm
by mitchandsuzy
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

Re: Identify New Rows, Common Rows and Original Rows

Posted: Fri Mar 24, 2017 1:33 pm
by h_gp9
Could you please send me your 1060.xlsx file?
I'm not a programmer only I'm an accountant :P
Thanks in advanced.

Re: Identify New Rows, Common Rows and Original Rows

Posted: Tue Mar 28, 2017 10:37 am
by mitchandsuzy
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

Re: Identify New Rows, Common Rows and Original Rows

Posted: Thu Mar 30, 2017 4:49 am
by h_gp9
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.

Re: Identify New Rows, Common Rows and Original Rows

Posted: Thu Mar 30, 2017 10:51 pm
by mitchandsuzy
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

Re: Identify New Rows, Common Rows and Original Rows

Posted: Sat Jun 17, 2017 12:53 am
by h_gp9
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.