cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DLROLLINGS
Helper I
Helper I

COMBINE TABLES AND COLUMNS

Firstly I would like to start this with saying I have a headache, a literally one. 

 

I have been asked to report on some data that, if I have my way, I would completely rewrite how they are recording the data. The basic principle is there are 120 spreadsheets in a Sharepoint Location that I need to combine. They are in 3 seperate folders and are all uniformed in the approach so simple enough...however the data tables as as below:

 

Property AddressOpt in Waiver Received DateOpt in Review Date (Waiver plus 6 month)WC 04/10/21WC 11/10/21WC 18/10/21
Address 106/06/202106/12/202106/10/2021DD/MM/YYYY21/10/2021
Address 2 NullDD/MM/YYYYDD/MM/YYYY21/10/2021

 

The WC dates continue for the full quarter of the year. I can combine all of the sheets within each folder using Power Query easily enough but then I get stuck. What I need to do from here is combine all of the data but in a means that makes much more sense.

 

Property AddressOpt in Waiver Received DateWaiver Review DateContact Due WeekContact Made
Address 106/06/2021

06/12/2021

04/10/202106/10/2021
Address 106/06/202106/12/202111/10/2021 
Address 106/06/202106/12/202118/10/202121/10/2021
Address 2  04/10/2021 
Address 2  11/10/2021 
Address 2  18/10/202121/10/2021

 

Any ideas on how I can achieve this? I have looked at UNION(SUMMARIZE...) but could not figure out how I combine 13 columns from 3 different sheet into 1 table whilst replicating every address and adding all relevant data.

 

Any help would be most appreciated. I have built an excel VBA that could do this for me but I would rather not have additional steps involved and would like to link direct to Power Bi so that I can automate the refresh. 

1 ACCEPTED SOLUTION
CNENFRNL
Super User
Super User

No point resorting to DAX for dataset transformation given that PQ handles it with ease,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxJKUotLlYwVNJRMtM30zcyMIIwDY2Q2AYwtouLvq+vfiQQADlGhnCZWB2EUUZAKQhCUY1TaywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Property Address" = _t, #"Opt in Waiver Received Date" = _t, #"Opt in Review Date (Waiver plus 6 month)" = _t, #"WC 04/10/21" = _t, #"WC 11/10/21" = _t, #"WC 18/10/21" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Opt in Waiver Received Date", type date}, {"Opt in Review Date (Waiver plus 6 month)", type date}}, "de"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Property Address", "Opt in Waiver Received Date", "Opt in Review Date (Waiver plus 6 month)"}, "Contact Due Week", "Contact Made"),
    Xform = Table.TransformColumns(#"Unpivoted Columns", {{"Contact Due Week", each Date.From(Text.Select(_, {"0".."9","/"}), "fr")}, {"Contact Made", each try Date.From(_, "fr") otherwise null}})
in
    Xform

Screenshot 2021-11-27 032129.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

1 REPLY 1
CNENFRNL
Super User
Super User

No point resorting to DAX for dataset transformation given that PQ handles it with ease,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxJKUotLlYwVNJRMtM30zcyMIIwDY2Q2AYwtouLvq+vfiQQADlGhnCZWB2EUUZAKQhCUY1TaywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Property Address" = _t, #"Opt in Waiver Received Date" = _t, #"Opt in Review Date (Waiver plus 6 month)" = _t, #"WC 04/10/21" = _t, #"WC 11/10/21" = _t, #"WC 18/10/21" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Opt in Waiver Received Date", type date}, {"Opt in Review Date (Waiver plus 6 month)", type date}}, "de"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Property Address", "Opt in Waiver Received Date", "Opt in Review Date (Waiver plus 6 month)"}, "Contact Due Week", "Contact Made"),
    Xform = Table.TransformColumns(#"Unpivoted Columns", {{"Contact Due Week", each Date.From(Text.Select(_, {"0".."9","/"}), "fr")}, {"Contact Made", each try Date.From(_, "fr") otherwise null}})
in
    Xform

Screenshot 2021-11-27 032129.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!