Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 Address | Opt in Waiver Received Date | Opt in Review Date (Waiver plus 6 month) | WC 04/10/21 | WC 11/10/21 | WC 18/10/21 |
Address 1 | 06/06/2021 | 06/12/2021 | 06/10/2021 | DD/MM/YYYY | 21/10/2021 |
Address 2 | Null | DD/MM/YYYY | DD/MM/YYYY | 21/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 Address | Opt in Waiver Received Date | Waiver Review Date | Contact Due Week | Contact Made |
Address 1 | 06/06/2021 | 06/12/2021 | 04/10/2021 | 06/10/2021 |
Address 1 | 06/06/2021 | 06/12/2021 | 11/10/2021 | |
Address 1 | 06/06/2021 | 06/12/2021 | 18/10/2021 | 21/10/2021 |
Address 2 | 04/10/2021 | |||
Address 2 | 11/10/2021 | |||
Address 2 | 18/10/2021 | 21/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.
Solved! Go to Solution.
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
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 still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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
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 still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.