Import complex CSV file

[Daniel_L]

Hello,

Help needed... I have a quite messy complex CSV file that I'm trying to import to Power BI. I guess some pivoting/unpivoting and fill commands are needed, but I don't know how to get it done.

This is what the source CSV file looks like:

Category	Account	Text	Code	Amount	Unit	Price per unit	Total
Financial account	47787	Org B					4107871
Main activity		Head org	AC.77	1	pcs	2750033
Resource		Res 12		4	pcs	50	200
Sub activity		Annual costs		1	year	5794152	2749833
Sub activity:2		Cat ABC		2	pcs	433095	792075
Resource		Res 1		5	pcs	141415	707075
Resource		Res 44		1	pcs	85000	85000
Sub activity:2		Cat DEF		2	pcs	482091	498474
Resource		Res 45		45	kg	6890	310050
Resource		Res 48		1	pcs	188424	188424
Sub activity:2		Cat EFG		5	pcs	293423,4	82551
Resource		Res AB		18	m	368	6624
Resource		Res R12		1	pcs	1452	1452
Resource		Res R11		3	pcs	22500	67500
Resource		Res R03		155	m	45	6975
Sub activity:2		Tesla		1	pcs	454920	454920
Resource		Res R99		1	pcs	454920	454920
Sub activity:2		Chevy		5	pcs	241108	523758
Resource		Res 3		2	pcs	261579	523158
Resource		Res 21		40	m	15	600
Sub activity:2		Car 2		2	pcs	199027,5	398055
Resource		Res 98		1	pcs	170595	170595
Resource		Res T4		1	pcs	170595	170595
Resource		Res EE		5	pcs	11373	56865
Main activity		Main act 3	SS.50	1	pcs	1357838
Sub activity		Subact RR2		3	pcs	113730	342040
Resource		Res 5		2	pcs	170595	341190
Resource		Res GG		1	pcs	850	850
Sub activity:2		Annual cat B		1	year	96960	96960
Resource		Res ert		30	weeks	3232	96960
Sub activity		Res cat G		1	year	1015798	1015798
Sub activity:2		Res cat H		1	year	529114	1015798
Sub activity:3		Other res		6	pcs	169300	1015798
Resource		Res JK		2	pcs	33000	66000
Resource		Res RR		22	pcs	25000	550000
Resource		Res RW		14	pcs	28557	399798
Financial account	11474	Org A					405910
Main activity		Organization A	CB.33	1	pcs	405910
Sub activity		Act OOK		1	year	405910	405910
Sub activity:2		Subact AA		2	pcs	201982	403964
Resource		Resource 22		1	pcs	348116	348116
Resource		Resource 23		1	pcs	55848	55848
Sub activity:2		Act EETRE		2	pcs	973	1946
Resource		Unit 556		3	pcs	554	1662
Resource		Unit 1323		2	pcs	142	284

And this is the goal after import:

Financial account	Code	Main activity	Sub activity	Sub activity:2	Sub activity:3	Resource	Amount	Unit	Price per unit
47787	AC.77	Head org				Res 12	4	pcs	50
47787	AC.77	Head org	Annual costs	Cat ABC		Res 1	5	pcs	141415
47787	AC.77	Head org	Annual costs	Cat ABC		Res 44	1	pcs	85000
47787	AC.77	Head org	Annual costs	Cat DEF		Res 45	45	kg	6890
47787	AC.77	Head org	Annual costs	Cat DEF		Res 48	1	pcs	188424
47787	AC.77	Head org	Annual costs	Cat EFG		Res AB	18	m	368
47787	AC.77	Head org	Annual costs	Cat EFG		Res R12	1	pcs	1452
47787	AC.77	Head org	Annual costs	Cat EFG		Res R11	3	pcs	22500
47787	AC.77	Head org	Annual costs	Cat EFG		Res R03	155	m	45
47787	AC.77	Head org	Annual costs	Tesla		Res R99	1	pcs	454920
47787	AC.77	Head org	Annual costs	Chevy		Res 3	2	pcs	261579
47787	AC.77	Head org	Annual costs	Chevy		Res 21	40	m	15
47787	AC.77	Head org	Annual costs	Car 2		Res 98	1	pcs	170595
47787	AC.77	Head org	Annual costs	Car 2		Res T4	1	pcs	170595
47787	AC.77	Head org	Annual costs	Car 2		Res EE	5	pcs	11373
47787	AC.77	Main act 3	Subact RR2	Car 2		Res 5	2	pcs	170595
47787	AC.77	Main act 3	Subact RR2	Car 2		Res GG	1	pcs	850
47787	AC.77	Main act 3	Subact RR2	Annual cat B		Res ert	30	weeks	3232
47787	AC.77	Main act 3	Res cat G	Res cat H	Other res	Res JK	2	pcs	33000
47787	AC.77	Main act 3	Res cat G	Res cat H	Other res	Res RR	22	pcs	25000
47787	AC.77	Main act 3	Res cat G	Res cat H	Other res	Res RW	14	pcs	28557
11474	CB.33	Organization A	Act OOK	Subact AA		Resource 22	1	pcs	348116
11474	CB.33	Organization A	Act OOK	Subact AA		Resource 23	1	pcs	55848
11474	CB.33	Organization A	Act OOK	Act EETRE		Unit 556	3	pcs	554
11474	CB.33	Organization A	Act OOK	Act EETRE		Unit 1323	2	pcs	142

Described in words: I wan't a table of all Resources with corresponding attributes which are fetched from lines above in the CSV.

Sample CSV file

Excel file with explainations, wanted result, hierarchy explanation

[dufoq3 · Accepted solution]

Hi @Daniel_L, different approach here.

In your expected result - there is a mistake in my opinion. This one should be correct:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVZbb5swFP4rKM9R5dvxZW8kI6k2TZHSTnuo+sAy1KJ2ZCKkU/frd+zYQGuMqgh8IHznO3f77m6xqZuyOdTlc1YeDsdz0y2WC6GUVrju2odshWv27hKU4Ad0cb+8W3wr6wahXf1Sd6/+/+uq/JUd2wcU8/WVsqooXn8OJ7wzBYRw7j61CvbV6XhuD5XH4mNGWSDqUUAslBAHuTn/fE+ZN80ZfTgcT93Jv7KUr1XZWrQyggJz7MJoZH+v5lOgXJddlq/W/on1BgjOiQEUlGFEQdJ0L0MPpAJ/DkjUDFCIkdkXpMZAkX6dtfhzsYkt1owYqw09FkokiSEE2wpPNmlSG8vLKSFAkjgdGUy1FkwMwqzJxWYbxYoZLhhfWhWaAdAUdx6qklojfltbpZWkZEk/931VjQwWrijckoSFlPLBTAYuMdKWchJIeOAD8Fa6EEvjq2AyNLfV6bmMDBUgsOwGIcVpzIeg00l5rF5CO41SIiglNrbAuAKdYg6+DuXHJMW2uwBpGshCeAXxQXLNIucrvs1YREiNIUwtLZobTSDZamaichUB191eSCBv4yb9ILIo4sFAubJhA6klJGdpeOcifHNz5QbhiJ6D0vziUGI04iuL3+9DyPhbE1yrC0ZEsqzChGCx1xzLwySB29Dib4aavyfTG2Y5DonVCO9nuZFGkn5NEFdtF5y13/6tqidLzhlnb6AT4bJwS72NqSmxJa1HUtKHoOU61gLMUCrmlIRm2nWPVZu1VdjR5BB/abgbQWMdE3H48jVKHe5jl+Elyczw2gfcqKH9dgR2TSN/BI+HzZtpAOXa0gRbp44dGBVlQfbYkS8mjh1Yc5QkOwVhZVP/K7v62Dj8enXlzhmjWXjRMNMsOXbKbheiNspaDx1ZMZl532558GAUQEKNZk4FNzK1UbnHjMWbFReaUjkIs3AewQG027Iva7r50PaiuN0XkfnGTStqxCT196buMgAZzRgAV+u4NSdhlLN4/6DCndc0xun+Pw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Account = _t, Text = _t, Code = _t, Amount = _t, Unit = _t, #"Price per unit" = _t, Total = _t]),
    ReplaceBlankToNull = Table.TransformColumns(Source, {}, each if _ is text and Text.Trim(_) = "" then null else _),
    FilledDown = Table.FillDown(ReplaceBlankToNull,{"Account", "Code"}),
    FilteredRows = Table.SelectRows(FilledDown, each not List.Contains({null, "Financial account"}, [Category])),
    Categories = List.Buffer(List.Distinct(List.Select(FilteredRows[Category], each _ <> "Resource"))),
    Ad_Columns = List.Accumulate(
        Categories,
        FilteredRows,
        (s,c)=> Table.AddColumn(s, c, each if List.Contains({c, "Main activity"}, [Category]) then [Text] else null, type text) ),
    FilledDown2 = Table.FillDown(Ad_Columns, Categories),
    FilteredRows2 = Table.SelectRows(FilledDown2, each ([Category] = "Resource")),
    ReplaceValues = Table.ReplaceValue(FilteredRows2,
        each [Main activity],
        each null,
        (x,y,z)=> if x = y then z else x,
        List.Skip(Categories) ),
    RestoreTypes = Value.ReplaceType(ReplaceValues, Value.Type(FilteredRows2)),
    RemovedColumns = Table.RemoveColumns(RestoreTypes,{"Category", "Total"}),
    RenamedColumns = Table.RenameColumns(RemovedColumns,{{"Account", "Financial account"}, {"Text", "Resource"}}),
    ReorderedColumnsDynamic = Table.ReorderColumns(RenamedColumns,{"Financial account", "Code"} & Categories & {"Resource", "Amount", "Unit", "Price per unit"}),
    ChangedType = Table.TransformColumnTypes(ReorderedColumnsDynamic,{{"Financial account", Int64.Type}, {"Code", type text}, {"Resource", type text}, {"Amount", type number}, {"Unit", type text}, {"Price per unit", Currency.Type}})
in
    ChangedType

[Daniel_L]

After viewing the provided solution a bit more I also see that SubActivity:2 is missing SubActivity. I.e. "Cat DEF" should have SubActivity = "Annual costs".

[wdx223_Daniel]

I have changed the Code, please check it again.

[Daniel_L]

Wow, thank you very much for providing this solution! Just one minor detail missing, the column for the resource name. I guess I'll figure that out.

[wdx223_Daniel]

let
    Source = Csv.Document(File.Contents("Your_Csv_File_Address"),[Delimiter=";", Columns=10,QuoteStyle=QuoteStyle.None]),
    Custom1 = let
                 t=Table.PromoteHeaders(Source)
              in
                 Table.FromPartitions(
                                      "Financial Account",
                                      Table.ToRows(
                                                   Table.Group(
                                                               t,
                                                               "Account",
                                                               {
                                                                "c1",
                                                                each Table.Combine(
                                                                                   Table.Group(
                                                                                               Table.Skip(_),
                                                                                               "Category",
                                                                                               {
                                                                                                "c2",
                                                                                                each let
                                                                                                        a=Table.Group(
                                                                                                                      Table.Skip(_),
                                                                                                                      "Category",
                                                                                                                      {"c3",each _},
                                                                                                                      0,
                                                                                                                      (x,y)=>Byte.From((Text.StartsWith(x,"Sub activity") and Text.StartsWith(y,"Resource")) or (Text.StartsWith(y,"Sub activity") and Text.StartsWith(x,"Resource")))
                                                                                                                     ),
                                                                                                        b=[Main activity=[Text]{0},Code=[Code]{0}]
                                                                                                     in
                                                                                                        List.Accumulate(
                                                                                                                        Table.ToRows(a),
                                                                                                                        {#table(0,{}),b},
                                                                                                                        (x,y)=>if Text.StartsWith(y{0},"Resource")
                                                                                                                               then {x{0}&Table.FromRecords(Table.TransformRows(y{1},each x{1}&[Resource=[Text]]&[[Amount],[Unit],[Price per unit],[Total]])),x{1}}
                                                                                                                               else {x{0},x{1}&Record.FromTable(Table.FromColumns({y{1}[Category],y{1}[Text]},{"Name","Value"}))}
                                                                                                                       ){0}
                                                                                               },
                                                                                               0,
                                                                                               (x,y)=>Byte.From(Text.StartsWith(y,"Main activity"))
                                                                                              )[c2]
                                                                                  )
                                                               },
                                                               0,
                                                               (x,y)=>Byte.From(y<>"" and y<>null)
                                                              )
                                                  )
                                     )
in
    Custom1