Tranform listed pdf data into an unpivoted table

[Anonymous]

I am really struggling with a PDF import of invoices

Attached shows how it's coming in - there are four attributes: Invoice #, Job Number, P.O number and Client.

'Client' is a separate column, the other three are combined into just two columns showing title and value. 

I want four columns; Invoice #, Job Number, P.O Number, Client. With the values as rows. 

I tried transposing but it's creating hundreds of columns e.g. Job Number.1 Job.Number.2 etc

PLEASE HELP!!!

[Zubair_Muhammad]

@Anonymous 

First Unpivot Title and Client Columns using VALUES as values

Then Pivot that single column back

[Anonymous]

@Zubair_Muhammad , You might need to talk me through this - if I unpivot both Attribute and Value columns, I get the attached. I have a feeling you mean me to do something else. 

Sorry! Brain dead now thinking about this!

Thanks 

[Zubair_Muhammad]

@Anonymous 

Could you copy paste some sample data with expected results?

[Zubair_Muhammad]

@Anonymous 

Second time you have to PIVOT (NOT UNpivot)

Select the Attribute column and select "pivot column" from the Transform tab.

When the Pop Up window appears...use VALUES in the Values Column

[Anonymous]

@Zubair_Muhammad 

Ok I think I can see where you're going but I'm not getting it right. 

Attached are three images - the first one is what I get when I first unpivot Title and Client, and then Pivot Value.

The second is the M query code

The third is essentially what I have, and what I want to transform it into.

Thanks so much for helping with this 🙂My M QueryWhat I get with unpivot and pivotWhat I need to transform

[Zubair_Muhammad]

@Anonymous 

Please could you copy paste the "WhatIhave" Table's data  (Copiable format)

[Anonymous]

No idea if this will work, else I can email it to you??

Title	Value	Client
InvoiceDate	3/31/2019	Company 1
Invoice #	6001647	Company 1
Job Number	483809	Company 1
P.O. No	EP1060754	Company 1
InvoiceDate	4/30/2019	Company 1
Invoice #	6001751	Company 1
Job Number	484412	Company 1
P.O. No	EP1072606	Company 1
InvoiceDate	5/30/2019	Company 4
Invoice #	6001904	Company 4
Job Number	489048	Company 4
P.O. No	EP1072606	Company 4
InvoiceDate	1/31/2019	Company 2
Invoice #	6001518	Company 2
Job Number	471342	Company 2
P.O. No	EP1060754	Company 2
InvoiceDate	2/26/2019	Company 3
Invoice #	6001557	Company 3
Job Number	475339	Company 3
P.O. No	EP1060754	Company 3
InvoiceDate	3/31/2019	Company 3
Invoice #	6001648	Company 3
Job Number	480286	Company 3
P.O. No	EP1060754	Company 3
InvoiceDate	4/30/2019	Company 3
Invoice #	6001752	Company 3
Job Number	484411	Company 3
P.O. No	EP1072606	Company 3
InvoiceDate	5/30/2019	Company 3
Invoice #	6001905	Company 3
Job Number	489046	Company 3
P.O. No	EP1072606	Company 3
InvoiceDate	1/31/2019	Company 5
Invoice #	6001517	Company 5
Job Number	471343	Company 5
P.O. No	EP978922	Company 5
InvoiceDate	2/26/2019	Company 5
Invoice #	6001556	Company 5
Job Number	475340	Company 5
P.O. No	EP978922	Company 5
InvoiceDate	3/31/2019	Company 5
Invoice #	6001650	Company 5
Job Number	480287	Company 5
P.O. No	EP978922	Company 5
InvoiceDate	5/30/2019	Company 5
Invoice #	6001906	Company 5
Job Number	489050	Company 5
P.O. No	EP978922	Company 5
InvoiceDate	1/31/2019	Company 7
Invoice #	6001520	Company 7
Job Number	471362	Company 7
P.O. No	EP1060754	Company 7
InvoiceDate	3/31/2019	Company 7
Invoice #	6001649	Company 7
Job Number	480294	Company 7
P.O. No	EP1060754	Company 7
InvoiceDate	4/30/2019	Company 7
Invoice #	6001753	Company 7
Job Number	484421	Company 7
P.O. No	EP1072606	Company 7
InvoiceDate	5/31/2019	Company 7
Invoice #	6001908	Company 7
Job Number	489055	Company 7
P.O. No	EP1072606	Company 7
InvoiceDate	1/31/2019	Company 6
Invoice #	6001521	Company 6
Job Number	471345	Company 6
P.O. No	EP1022100	Company 6
InvoiceDate	2/26/2019	Company 6
Invoice #	6001560	Company 6
Job Number	475341	Company 6
P.O. No	EP1065856	Company 6

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Title", type text}, {"Value", type any}, {"Client", type text}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Merged", each Text.Combine({[Title], [Client]}, ":"), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Title", "Client"}),
    Partition = Table.Group(#"Removed Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Value", "Index"}, {"Value", "Index"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Partition", "Merged", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", type text}, {"Merged.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Merged.1]), "Merged.1", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"InvoiceDate", "Invoice #", "Job Number", "P.O. No", "Merged.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Merged.2", "Client"}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Renamed Columns", {{"InvoiceDate", type date}}, "en-US")
in
    #"Changed Type with Locale"

Hope this helps.

[Zubair_Muhammad · Accepted solution]

@Anonymous 

My apologies for late reply.

Please see the attached file after transforming your sample data.

Please let me know if it helps

Here is the M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdQ/T8MwEAXwrxKFtWrOZ5//zMAAQ+ledWhRB4Y2CAES3x4PCJHcc5yIblXzZP/ke7rdrn24fPYvz6e7w/upXbW2s6ZjMin/vu3Pr4fLV2Pa/eo319zkL57IeBdU5rE/NpuP8/H0lj+5aCPpc7brp3Wz6fP/91tDnoK40l0/JtdZmmcKYmom5wxXTIE9+YpJgMlBUyKnMiNTjkSVmTY5YDJgdgxNYqLKDE3BWMcqMz07Bibu2I9NFpskqMzIJNbqc6ZNFphQx7HJD+aCTJE4+iuYUMexKQjXTLnjpmIa9wmZUMexKZHUTLnjtXeaY0Idl0LHg8rojluV+WtKISbm0lUTFS+QxNdIYh39n4Qajkle9HW64folF5NQmTApUe2VcpkAezEJdSngwTGpjOqSZ5WZ3gNh5uSwyTudUZNL+r7lJrSbsCnXt2Zyjk3FNN4DyCSz3ylRrJlyneQKJtQnX+iTURm9m0RlhiZmQ1S6a2I5FUxen6WXk3aP+iRxsORyZv8N", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Title = _t, Value = _t, Client = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Title", type text}, {"Value", type text}, {"Client", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each [Index]-Number.Mod([Index],4)),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"all", each _, type table [Title=text, Value=text, Client=text, Index=number, Custom=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Text.Combine(
[all][Value]&List.Distinct([all][Client]),"|")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom1", "Custom.1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Invoice Date", "Invoice #", "Job Number", "P.O. No", "Client"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Invoice Date", type date}, {"Invoice #", Int64.Type}, {"Job Number", Int64.Type}, {"P.O. No", type text}, {"Client", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Custom", "all"})
in
    #"Removed Columns"

[Anonymous]

@Zubair_Muhammad YOU. Are an absolute STAR. 

I would never in a million years have been able to figure this out for myself, but it makes perfect sense now I've seen your steps - you are a genius of epic proportions and I will forever be in your debt. 

Utterly fantastic. 

Thank you SO much! 

[Zubair_Muhammad]

@Anonymous 

My apologies for late reply.

Please see the attached file after transforming your sample data.

Please let me know if it helps

Here is the M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdQ/T8MwEAXwrxKFtWrOZ5//zMAAQ+ledWhRB4Y2CAES3x4PCJHcc5yIblXzZP/ke7rdrn24fPYvz6e7w/upXbW2s6ZjMin/vu3Pr4fLV2Pa/eo319zkL57IeBdU5rE/NpuP8/H0lj+5aCPpc7brp3Wz6fP/91tDnoK40l0/JtdZmmcKYmom5wxXTIE9+YpJgMlBUyKnMiNTjkSVmTY5YDJgdgxNYqLKDE3BWMcqMz07Bibu2I9NFpskqMzIJNbqc6ZNFphQx7HJD+aCTJE4+iuYUMexKQjXTLnjpmIa9wmZUMexKZHUTLnjtXeaY0Idl0LHg8rojluV+WtKISbm0lUTFS+QxNdIYh39n4Qajkle9HW64folF5NQmTApUe2VcpkAezEJdSngwTGpjOqSZ5WZ3gNh5uSwyTudUZNL+r7lJrSbsCnXt2Zyjk3FNN4DyCSz3ylRrJlyneQKJtQnX+iTURm9m0RlhiZmQ1S6a2I5FUxen6WXk3aP+iRxsORyZv8N", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Title = _t, Value = _t, Client = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Title", type text}, {"Value", type text}, {"Client", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each [Index]-Number.Mod([Index],4)),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"all", each _, type table [Title=text, Value=text, Client=text, Index=number, Custom=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Text.Combine(
[all][Value]&List.Distinct([all][Client]),"|")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom1", "Custom.1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Invoice Date", "Invoice #", "Job Number", "P.O. No", "Client"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Invoice Date", type date}, {"Invoice #", Int64.Type}, {"Job Number", Int64.Type}, {"P.O. No", type text}, {"Client", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Custom", "all"})
in
    #"Removed Columns"